Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : [JobMgr_30_ApplProceduresObjects]'
Print ' Autor         : J.Feuerstein'
Print ' Creation Date : 22.03.2006'
Print ' Last Update   : 08.01.2014'
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO


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

CREATE PROCEDURE JobControl_ObjectsProcessData_tbl_Reset
AS
BEGIN

  /* alles was in P geblieben ist auf 'D' setzen */
  UPDATE JobControl_ObjectsProcessData_tbl SET ProcessState = 'D' WHERE ProcessState = 'P'

 /*
  UPDATE Objects_tbl SET ProcStateCol01 = 'N'  where ProcStateCol01 = '' or ProcStateCol01 is NULL
  Print 'DONE ProcStateCol01'
 -- UPDATE Objects_tbl SET ProcStateCol03 = 'N'  where ProcStateCol03 = '' or ProcStateCol03 is NULL
  UPDATE Objects_tbl SET ProcStateCol04 = 'N'  where ProcStateCol04 = '' or ProcStateCol04 is NULL
  Print 'DONE ProcStateCol04'
  UPDATE Objects_tbl SET ProcStateCol05 = 'N'  where ProcStateCol05 = '' or ProcStateCol05 is NULL
  Print 'DONE ProcStateCol05'
  UPDATE Objects_tbl SET ProcStateCol06 = 'N'  where ProcStateCol06 = '' or ProcStateCol06 is NULL
  Print 'DONE ProcStateCol06'
  UPDATE Objects_tbl SET ProcStateCol07 = 'N'  where ProcStateCol07 = '' or ProcStateCol07 is NULL
  Print 'DONE ProcStateCol07'
  UPDATE Objects_tbl SET ProcStateCol08 = 'N'  where ProcStateCol08 = '' or ProcStateCol08 is NULL
  Print 'DONE ProcStateCol08'
  UPDATE Objects_tbl SET ProcStateCol09 = 'N'  where ProcStateCol09 = '' or ProcStateCol09 is NULL
  Print 'DONE ProcStateCol09'
  UPDATE Objects_tbl SET ProcStateCol10 = 'N'  where ProcStateCol10 = '' or ProcStateCol10 is NULL
  Print 'DONE ProcStateCol10'
  */
END
GO


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

-- #New: 15.04.2013 / J.Fes
-- #Lup: 29.04.2016 / J.Fes add ObjectReleaseDate + ObjectReleaseState
Create Procedure Objects_tbl_ObjShortInfo  @ObjectPuid       nvarchar(64),
                                           @ObjectFromSiteID nvarchar(32)
-- gibt den Datensatz zur gesuchten ObjectPuid zurueck
As
BEGIN
     select Id ,
            ObjectLastModDate,
            ObjectOwningSiteID,
            ObjectReleaseDate,
            ObjectReleaseState,
            ObjectUgMaster_Type,
            ObjectUgMaster_CompCount,
            ObjectUgMaster_CompCountUnique,
            ObjectUgMaster_APLevels
       from Objects_tbl with (nolock)
        where (ObjectPuid      = @ObjectPuid
          and ObjectFromSiteID = @ObjectFromSiteID)
end
go


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

-- 03.04.2013 add @Id  uniqueidentifier,
Create Procedure Objects_tbl_Object_GetDS  @Id               uniqueidentifier = null,  -- #New: 05.04.2013
                                           @ObjectCliName    nvarchar(255),
                                           @ObjectName       nvarchar(255),  -- new 28.01.2011 09:48 J.Fes
                                           @ObjectPuid       nvarchar(64),   -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                                           @ObjectFromSiteID nvarchar(32),
                                           @ObjectClass      nvarchar(10)
-- gibt den Datensatz zur gesuchten ObjectPuid zurueck
-- Wichtig: ein Datensatz wird eindeutig durch @ObjectPuid und @ObjectFromSiteID @ObjectClass
--          oder durch @ObjectCliName und @ObjectFromSiteID @ObjectClass
-- with (nolock) 10.07.2010 16:38 eingefuegt!!
-- ergaenzung jetzt auch suche via @ObjectName integriert
-- @Id               nvarchar (64),
As
BEGIN

   if @Id is not null
    begin
     -- suche via @Id  #New: 05.04.2013
     select * from Objects_tbl with (nolock)
        where (ID = @Id)
     goto MyExit
    end

   if @ObjectPuid <> ''
    begin
     -- suche mit @ObjectPuid
     select * from Objects_tbl with (nolock)
        where (ObjectPuid      = @ObjectPuid
          and ObjectFromSiteID = @ObjectFromSiteID)
     goto MyExit
    end

    if @ObjectCliName <> ''
    begin
     -- suche mit @ObjectCliName
     select * from Objects_tbl with (nolock)
        where (ObjectCliName   = @ObjectCliName
          and ObjectFromSiteID = @ObjectFromSiteID
          and ObjectClass      = @ObjectClass)
     goto MyExit
    end

    if @ObjectName <> ''
    begin
       -- suche mit @ObjectName new 28.01.2011 09:47
       select * from Objects_tbl with (nolock)
          where (ObjectName      = @ObjectName
            and ObjectFromSiteID = @ObjectFromSiteID
            and ObjectClass      = @ObjectClass)
       goto MyExit
    end
  MyExit:
END
GO



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


Create Procedure Objects_tbl_Object_GetId  @ObjectCliName    nvarchar(255) ,
                                           @ObjectFromSiteID nvarchar(32) ,
                                           @ObjectClass      nvarchar(10) ,
                                           @Id               uniqueidentifier Output
-- gibt die ID zum Datensatz des gesuchten ObjectNamens zurueck
-- wichtig ein DataSet Datensatz wird eindeutig durch [ObjectCliName], [ObjectFromSiteID], [ObjectClass]
-- WITH (nolock) eingefuegt  10.07.2010 16:38 J.fes
As
BEGIN
  select @Id=Id from Objects_tbl with (nolock)
         where ObjectCliName    = @ObjectCliName
           and ObjectFromSiteID = @ObjectFromSiteID
           and ObjectClass      = @ObjectClass
END
GO

-- Create Procedure Objects_tbl_Object_GetId2 @ObjectCliName    nvarchar(255) ,
--                                            @ObjectFromSiteID nvarchar(32) ,
--                                            @ObjectClass      nvarchar(10) ,
--                                            @Id               uniqueidentifier Output
-- -- gibt die ID zum Datensatz des gesuchten ObjectNamens zurueck
-- -- wichtig ein DataSet Datensatz wird eindeutig durch [ObjectCliName], [ObjectFromSiteID], [ObjectClass]
-- -- WITH (nolock) eingefuegt  10.07.2010 16:38 J.fes
-- As
-- BEGIN
--   select @Id=Id from Objects_tbl with (nolock)
--          where ObjectCliName    = @ObjectCliName
--            and ObjectFromSiteID = @ObjectFromSiteID
--            and ObjectClass      = @ObjectClass
--            and (ObjectPuid is NULL or ObjectPuid = '')
-- END
-- GO




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


Create Procedure Objects_tbl_Object_GetId_via_ObjectPuid
                                           @ObjectPuid       nvarchar(64) ,     -- change A.Becker 22.08.2012: Puid Feld von 15 auf 32 Zeichen erweitert
                                           @ObjectCliName    nvarchar(255) ,
                                           @ObjectFromSiteID nvarchar(32) ,
                                           @ObjectClass      nvarchar(10) ,
                                           @IrId             uniqueidentifier,
                                           @IrPuid           nvarchar(64),
                                           @Id_r             uniqueidentifier Output
-- gibt die ID zum Datensatz des gesuchten Objects zurueck
-- wichtig ein DataSet Datensatz wird eindeutig durch [@ObjectPuid], [ObjectFromSiteID], [ObjectClass]
-- 24.02.2011 J.Fes @IrId eingefuegt um feststellen zu koennen ob ein DS mehrfach referenziert ist
-- 15.04.2013 J.Fes anpassungen siehe weiter unten
-- 18.08.2014 J.Fes anpassungen siehe weiter unten
-- 20.10.2014 J.Fes anpassungen siehe weiter unten
-- 19.02.2015 J.Fes entfernt @IrPuid ,  @IrId

As
BEGIN

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

 set @MyProcName='Objects_tbl_Object_GetId_via_ObjectPuid'

--  if @IrId = '58b73fa7-0bc3-4776-bf37-047b21f6c4a5'
--  begin
     -- set @DebugPrintOn = 1
--  end


 if @DebugPrintOn = 1
 begin
  -- Clear Debug Table
  -- EXECUTE zzzzzDebugTbl_Empty
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '###########################'       , ###########################
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectPuid'       , @ObjectPuid
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectCliName'    , @ObjectCliName
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ObjectFromSiteID' , @ObjectFromSiteID
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@ObjectClass'      , @ObjectClass
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05-@IrId'             , @IrId
  EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '06-@IrPuid'           , @IrPuid
 end

 set @Id_r = NULL
 -- #Doc:01.12.2010 18:38 suche via PuiD da sich der ObjectCliName eventuell aendert hat
 if (NOT @ObjectPuid is NULL) AND (@ObjectPuid <> '')
   BEGIN
--     select @Id_r=Objects_tbl.Id from Objects_tbl WITH (nolock)
--               where ObjectPuid       = @ObjectPuid
--                 and ObjectFromSiteID = @ObjectFromSiteID
--                 and ObjectClass      = @ObjectClass
-- --     if @Id_r is NULL
-- --       BEGIN
-- --         -- #Doc: wenn bis hier nicht gefunden wurde dann via Objects_tbl_Object_GetId nach der ID suchen
-- --         -- #Doc: 02.04.2015 16:14 eingefügt um bei evtl fehlenden PUIDS die referenz daten dennoch zu finden
-- --         EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id_r Output
-- --       END


     if @IrId is NULL
       BEGIN -- *************************
         if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10.1 Get:@Id @IrId=NULL @ObjectPuid=', @ObjectPuid
         select @Id_r=Id FROM Objects_tbl WITH (nolock)
                   WHERE ObjectPuid       = @ObjectPuid
                     AND ObjectFromSiteID = @ObjectFromSiteID
                     AND ObjectClass      = @ObjectClass

--          -- #New 14.10.2015 J.Fes
--          -- find @IR_r of ObjectPuid = '' via ObjectCliName=@ObjectCliName
--          if @Id_r is NULL
--          BEGIN
--          select @Id_r=Id from Objects_tbl WITH (nolock)
--                    where (ObjectPuid      = '' or ObjectPuid is NULL)
--                      and ObjectFromSiteID = @ObjectFromSiteID
--                      and ObjectClass      = @ObjectClass
--                      and ObjectCliName    = @ObjectCliName
--          END

         if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10.2 Ret:@Id @IrId=NULL @Id=', @Id_r
       END   -- *************************
       ELSE
       BEGIN -- *************************

         /* SQL Stand bis 01.03.2011 dieser hatte dazugeführt das ein Dataset mit mehreren
            referenzen auch im JobManger mehrfach referenziert ist */
         /*
         select @Id=Id from Objects_tbl WITH (nolock)
                   where ObjectPuid       = @ObjectPuid
                     and ObjectFromSiteID = @ObjectFromSiteID
                     and ObjectClass      = @ObjectClass
           */

         if @DebugPrintOn = 1
         begin
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.1 Get:@Id @IrId<>NULL @ObjectPuid=', @ObjectPuid
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.2 Get:@Id @IrId<>NULL @ObjectFromSiteID=', @ObjectFromSiteID
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.3 Get:@Id @IrId<>NULL @ObjectClass=', @ObjectClass
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.4 Get:@Id @IrId<>NULL @IrId=', @IrId
         end

         -- find JobMgr @Id_r via @ObjectPuid
         -- #LUp: 26.08.2019/J.Fes A.Stein #Issue: Perf bad if only 1 Ir reffers all datasets
         -- #LUp: 30.08.2019/J.Fes/A.Becker/A.Stein: Index Update
         --                               |-> see JobMgr_11_DataTable_Objects_tbl.sql 727 .. EXECUTE JF_IndexCheckCreateDef 'Objects_tbl_IDX20_V2' ..
         SELECT @Id_r=Objects_tbl.Id
         FROM
           Objects_tbl WITH (nolock)
           INNER JOIN Objects_Ir_Rel_tbl WITH (nolock) ON (Objects_tbl.Id = Objects_Ir_Rel_tbl.ObjectId)
         WHERE
           ObjectPuid           = @ObjectPuid
           and ObjectFromSiteID = @ObjectFromSiteID
           and ObjectClass      = @ObjectClass
           and Objects_Ir_Rel_tbl.IrId = @IrId

         -- #New 14.10.2015 J.Fes
         -- find @IR_r of ObjectPuid = '' via ObjectCliName=@ObjectCliName
         if @Id_r is NULL
         BEGIN
          SELECT @Id_r=Objects_tbl.Id
          FROM
            Objects_tbl WITH (nolock)
            INNER JOIN Objects_Ir_Rel_tbl WITH (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
          WHERE
            (ObjectPuid          = '' or ObjectPuid is NULL)
            and ObjectFromSiteID = @ObjectFromSiteID
            and ObjectClass      = @ObjectClass
            and ObjectCliName    = @ObjectCliName
            and dbo.Objects_Ir_Rel_tbl.IrId = @IrId
         END

         if @DebugPrintOn = 1
         BEGIN
          EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '20.5 Ret:@Id @IrId<>NULL @Id=', @Id_r
         END

     end  -- *************************

   END
   ELSE -- #Doc: 05.02.2015 eingefuegt da es gefehlt hatte
   BEGIN -- Wenn @ObjectPuid NULL oder leer ist
     -- #Doc: 20.10.2014 15:26/J.Fes aktiviert fuer den Fall das keine @ObjectPuid gegeben ist
     EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id_r Output
   END
END
GO




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


Create Procedure Objects_tbl_Object_GetId_UgMasterDS_of_IR
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @Ir_ObjectClass      nvarchar(10) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectName       nvarchar(255) Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output ,
                                   @DS_ObjectClass      nvarchar(255) Output
-- gibt die ID des Dataset UGmaster zurück ausgehend von einer IR
-- wichtig ein DataSet Datensatz wird eindeutig durch [@ObjectPuid], [ObjectFromSiteID], [ObjectClass]
As
BEGIN
  select @Id=DS.Id , @DS_ObjectName=DS.ObjectName, @DS_ObjectClass=DS.ObjectClass , @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    INNER JOIN dbo.Objects_Ir_Rel_tbl WITH (NOLOCK) ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
    INNER JOIN dbo.Objects_tbl DS WITH (NOLOCK) ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
  WHERE
    IR.ObjectCliName = @Ir_ObjectCliName AND
    IR.ObjectClass = 'IR' AND
    IR.ObjectFromSiteID = @Ir_ObjectFromSiteID AND
    DS.ObjectType = 'UGMASTER'
END
GO



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

Create Procedure Objects_tbl_Object_GetId_DS_of_IR_viaObjectID
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @DS_ObjectName       nvarchar(255) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output
-- gibt die ID des Dataset von IR mit DS.ObjectName=@DS_ObjectName new 15.02.2012
-- wichtig ein DataSet Datensatz wird eindeutig durch [@Ir_ObjectFromSiteID ], [@Ir_ObjectCliName], [@DS_ObjectName]
As
BEGIN
  select top 1
    @Id=DS.Id ,
    @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    INNER JOIN dbo.Objects_Ir_Rel_tbl WITH (NOLOCK) ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
    INNER JOIN dbo.Objects_tbl DS WITH (NOLOCK) ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
  WHERE
    IR.ObjectCliName = @Ir_ObjectCliName
    And IR.ObjectClass = 'IR'
    And IR.ObjectFromSiteID = @Ir_ObjectFromSiteID
    And DS.ObjectName = @DS_ObjectName
END
GO


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


Create Procedure Objects_tbl_Object_GetId_DS_of_IR_viaObjectType_UGMASTER
                                   @Ir_ObjectCliName    nvarchar(255) ,
                                   @Ir_ObjectFromSiteID nvarchar(32) ,
                                   @DS_ObjectName       nvarchar(255) ,
                                   @Id                  uniqueidentifier Output ,
                                   @DS_ObjectCliName    nvarchar(255) Output
-- gibt die ID des Dataset von IR mit DS.ObjectName=@DS_ObjectName new 15.02.2012
-- wichtig ein DataSet Datensatz wird eindeutig durch [@Ir_ObjectFromSiteID ], [@Ir_ObjectCliName], [@DS_ObjectName]
As
BEGIN
  select top 1
    @Id=DS.Id ,
    @DS_ObjectCliName=DS.ObjectCliName
  FROM
    dbo.Objects_tbl IR WITH (NOLOCK)
    INNER JOIN dbo.Objects_Ir_Rel_tbl WITH (NOLOCK) ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
    INNER JOIN dbo.Objects_tbl DS WITH (NOLOCK) ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
  WHERE
    IR.ObjectCliName = @Ir_ObjectCliName
    AND IR.ObjectClass = 'IR'
    AND IR.ObjectFromSiteID = @Ir_ObjectFromSiteID
    AND DS.ObjectType = 'UGMASTER'
END
GO

Print '-- [Objects_tbl_Object_Get_ProcessStatus_UDF] -------------------------------'
If Exists (select Name
           from sysobjects
            where Name = 'Objects_tbl_Object_Get_ProcessStatus_UDF')
DROP FUNCTION Objects_tbl_Object_Get_ProcessStatus_UDF
GO


-- returs for @Id value off @ProcStateCol
CREATE FUNCTION Objects_tbl_Object_Get_ProcessStatus_UDF(@Id uniqueidentifier, @ProcStateCol nvarchar(2))
RETURNS nvarchar(32)
AS
BEGIN
    DECLARE @ProcStateColVal_r nvarchar(32)
    SELECT @ProcStateColVal_r = CASE @ProcStateCol
       WHEN '01' THEN [ProcStateCol01]
       WHEN '02' THEN [ProcStateCol02]
       WHEN '03' THEN [ProcStateCol03]
       WHEN '04' THEN [ProcStateCol04]
       WHEN '05' THEN [ProcStateCol05]
       WHEN '06' THEN [ProcStateCol06]
       WHEN '07' THEN [ProcStateCol07]
       WHEN '08' THEN [ProcStateCol08]
       WHEN '09' THEN [ProcStateCol09]
       WHEN '10' THEN [ProcStateCol10]
       WHEN '11' THEN [ProcStateCol11]
       WHEN '12' THEN [ProcStateCol12]
       WHEN '13' THEN [ProcStateCol13]
       WHEN '14' THEN [ProcStateCol14]
       WHEN '15' THEN [ProcStateCol15]
       WHEN '16' THEN [ProcStateCol16]
       WHEN '17' THEN [ProcStateCol17]
       WHEN '18' THEN [ProcStateCol18]
       WHEN '19' THEN [ProcStateCol19]
       WHEN '20' THEN [ProcStateCol20]
       WHEN 'Mc' THEN [ProcStateColMc]
       WHEN 'Rf' THEN [ProcStateColRf]
     END
       FROM Objects_tbl WITH (nolock)
       WHERE Objects_tbl.Id = @Id

    RETURN @ProcStateColVal_r
END
GO


Print '-- [ObjectfindBy_FromSiteClassObjectName #New:19.01.2017/J.Fes] ---------'
If Exists (select Name from sysobjects
           where Name = "ObjectfindBy_FromSiteClassObjectName" and type = "P")
Drop Procedure ObjectfindBy_FromSiteClassObjectName
GO

Create Procedure ObjectfindBy_FromSiteClassObjectName
                                   @ObjectFromSiteID nvarchar (32) ,
                                   @ObjectClass      nvarchar (10) , -- I DS IR
                                   @ObjectName       nvarchar (32) ,
                                   @Id_r             uniqueidentifier Output ,
                                   @ObjectCliName_r  nvarchar(255) Output
-- returns @Id_r and @ObjectCliName_r for @ObjectFromSiteID, @ObjectClass, @ObjectName
-- This methode can be used in Databeses where it ensured that:
--- @ObjectFromSiteID, @ObjectClass, @ObjectName is UNIC
As
BEGIN
  select top 1
    @Id_r=Id ,
    @ObjectCliName_r=ObjectCliName
  FROM
    dbo.Objects_tbl
  WHERE
    ObjectClass = @ObjectClass
    AND ObjectFromSiteID = @ObjectFromSiteID
    AND ObjectName = @ObjectName
END
GO



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

Create Procedure Objects_Ir_Rel_tbl_GetId  @IrId        uniqueidentifier,
                                           @ObjectId    uniqueidentifier,
                                           @Id          uniqueidentifier Output
-- gibt die ID zum Datensatz zurück
-- 11.07.2010 14:27 WITH (NOLOCK) ergänzt
As
BEGIN
  select @Id=Id from Objects_Ir_Rel_tbl WITH (NOLOCK)
         where IrId      = @IrId
           and ObjectId  = @ObjectId
END
GO


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

Create Procedure Objects_Ir_Rel_Create  @IrId              uniqueidentifier,
                                        @ObjectId          uniqueidentifier,
                                        @DataSet_Ir_RelId  uniqueidentifier Output
-- fuegt in die Tabelle Objects_Ir_Rel_tbl die Releation ein
-- 12.07.2010 09:14 J.Fes new
As
BEGIN
 EXECUTE Objects_Ir_Rel_tbl_GetId @IrId ,@ObjectId  , @DataSet_Ir_RelId Output

 DECLARE @RetryCounter INT
 SET @RetryCounter = 1
 RETRY: -- Label RETRY

 BEGIN TRY          -- *************  Begin TRY ************
  BEGIN TRANSACTION  -- *************  BEGIN Transaction **************  Verlegt 03.09.2010 14:35 J.Fes/A.Becker
   -- referenzen IR - Dataset anlegen
  if @DataSet_Ir_RelId IS NULL
    BEGIN
     SET @DataSet_Ir_RelId = NEWID()
     INSERT INTO Objects_Ir_Rel_tbl (ID ,                IrId , ObjectId)
                 VALUES             (@DataSet_Ir_RelId ,@IrId ,@ObjectId)
    END
 COMMIT TRANSACTION   -- *************  Commit Transaction **************
 END TRY              -- *************  End TRY **************

 BEGIN CATCH          -- *************  Begin CATCH ************
 	-- PRINT 'Rollback Transaction'
 	ROLLBACK TRANSACTION
 	DECLARE @DoRetry bit; -- Whether to Retry transaction or not
 	DECLARE @ErrorMessage varchar(500)
 	SET @DoRetry = 0;
 	SET @ErrorMessage = ERROR_MESSAGE()
 	IF ERROR_NUMBER() = 1205 -- Deadlock Error Number
 	BEGIN
 		SET @DoRetry = 1; -- Set @DoRetry to 1 only for Deadlock
 	END
 	IF @DoRetry = 1
 	BEGIN
 		SET @RetryCounter = @RetryCounter + 1 -- Increment Retry Counter By one
 		IF (@RetryCounter > 3) -- Check whether Retry Counter reached to 3
 		BEGIN
 			RAISERROR(@ErrorMessage, 18, 1) -- Raise Error Message if still deadlock occurred after three retries
 		END
 		ELSE
 		BEGIN
    	WAITFOR DELAY '00:00:00.10' -- 02.08.2010 19:35 Wait=01 sec 23.07.2010 01:30 Wait=01 sec --- Wait for 10 ms
 			GOTO RETRY	-- Go to Label RETRY
 		END
 	END
 	ELSE
 	BEGIN
 		RAISERROR(@ErrorMessage, 18, 1)
 	END
 END CATCH    -- *************  End CATCH ************
END
GO



Print '-- [ObjIds_View] --------------------------------------------------------'
If Exists (select Name
           from sysobjects
           where Name = "ObjIds_View" and type = "V")
Drop View ObjIds_View
GO


CREATE VIEW [ObjIds_View]
-- Returns SubObj Id's of RootId
-- I  -> IR
-- IR -> DS
AS
SELECT
  OSub.Id as Id,
  ORoot.Id as RootId
FROM
  dbo.Objects_tbl ORoot
  JOIN dbo.Objects_Ir_Rel_tbl [Rel] ON (ORoot.Id = [Rel].IrId)
  JOIN dbo.Objects_tbl OSub ON ([Rel].ObjectId = OSub.Id)
GO


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

CREATE PROCEDURE Objects_tbl_IdsFromORootGet
                 @ObjectId uniqueidentifier

AS
BEGIN

  if (object_id('tempdb..#zzIt_tbl') is not null)  Drop table #zzIt_tbl
  if (object_id('tempdb..#zzIr_tbl') is not null)  Drop table #zzIr_tbl
  if (object_id('tempdb..#zzDs_tbl') is not null)  Drop table #zzDs_tbl

  CREATE TABLE #zzIt_tbl(Id uniqueidentifier)
  insert into #zzIt_tbl (Id) Values(@ObjectId)

  -- select IR's of Item
  SELECT Id
   into #zzIr_tbl
   from ObjIds_View WHERE  RootId in (select ID from #zzIt_tbl)

  -- select DS of IR's
  SELECT Id
   into #zzDs_tbl
   from ObjIds_View WHERE  RootId in (select ID from #zzIr_tbl)

  select * from #zzDs_tbl
  union all (SELECT * from #zzIr_tbl)
  union all (SELECT * from #zzIt_tbl)
END
GO

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

CREATE PROCEDURE Objects_tbl_DeleteData @DeleteMethod  nvarchar (64) , -- Delete Methode
                                        @Object_tbl_Id nvarchar (64) , -- Objects_tbl.ID
                                        @ObjectClass   nvarchar (10) , -- I,IR oder DS
                                        @PDataId       nvarchar (64)   -- Process Datensatz ID kann leer sein

/* new 03.01.2011 J.Fes
   loescht von Object Daten aus den Tabellen:
   -- Objects_tbl
   -- Objects_ir_Rel_tbl
   -- JobControl_ObjectsProcessData_tbl

   -- Example Data: tceng: BgStrukEx-KBG-03_01/A

   -- loesch Methoden:
   -- M0_NON  0 - nichts löschen
   -- M1_PDS  1 - Process data:'selected'
   -- M2_PDA  2 - Process data:'all'
   -- M3_DSS  3 - Dataset(s) and all Process data
   -- M4_IRS  4 - Ir(s) including all Dataset and all Process data
   -- M5_ITM  5 - löschen aller der Daten eines ITEMS
   -- M6_OID  6 - löscht Object_tbl.ID
  */
AS
BEGIN

  DECLARE @DebugPrintOn bit
  DECLARE @I            integer
  DECLARE @MyProcName   nvarchar(32)
  DECLARE @IrId         nvarchar(64)

  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_DeleteData'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@DeleteMethod' , @DeleteMethod
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ObjectClass'  , @ObjectClass
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@Object_tbl_Id', @Object_tbl_Id
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@PDataId'      , @PDataId
  end


  -- Exit wenn keine @Object_tbl_Id gegeben ist
  if @Object_tbl_Id = "" Goto EndeExit
  if @DeleteMethod = "M0_NON" Goto EndeExit

  -- Delete
  if @DeleteMethod = 'M6_OID'
  begin
    DELETE FROM dbo.Objects_tbl
       where dbo.Objects_tbl.Id = @Object_tbl_Id
    Goto EndeExit
  end


  if @ObjectClass = 'IR'
     begin -- Daten löschen via IR (new 25.01.2011)

      -- EXECUTE Objects_tbl_Object_GetId @ObjectCliName, @ObjectFromSiteID, @ObjectClass, @Id Output
      -- if @Object_tbl_Id = "" Goto EndeExit

      -- if @DebugPrintOn = 1
      -- Step: delete result of datasets from dbo.JobControl_ObjectsProcessData_tbl
      DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
       WHERE
         JobControl_ObjectsProcessData_tbl.Objects_tbl_ID IN
         (SELECT DS.Id FROM dbo.Objects_tbl
           IR INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
              INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
           WHERE IR.Id = @Object_tbl_Id)

      -- Step: delete Datasets of Ir from Objects_tbl
      DELETE FROM dbo.Objects_tbl
       where dbo.Objects_tbl.Id in
       (SELECT
         DS.Id
        FROM
          dbo.Objects_tbl IR
          INNER JOIN dbo.Objects_Ir_Rel_tbl ON (IR.Id = dbo.Objects_Ir_Rel_tbl.IrId)
          INNER JOIN dbo.Objects_tbl DS ON (dbo.Objects_Ir_Rel_tbl.ObjectId = DS.Id)
        WHERE
          IR.Id = @Object_tbl_Id)

      -- Step: delete datasets Relation from dbo.Objects_Ir_Rel_tbl
      DELETE FROM dbo.Objects_Ir_Rel_tbl
       WHERE
        dbo.Objects_Ir_Rel_tbl.IrId = @Object_tbl_Id

      -- Step: delete IR
      DELETE FROM dbo.Objects_tbl
       WHERE
        Id=@Object_tbl_Id
     end   -- ende Daten löschen via IR

  else  --------- !!!!!!!!!!!!! ELSE !!!!!!!!! -----------------------------

 		  begin -- Daten löschen ausgehend von einem DataSet

       if @DeleteMethod = "M1_PDS" and @PDataId <> ""
          begin
            DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
              WHERE dbo.JobControl_ObjectsProcessData_tbl.ID = @PDataId

            -- Step: Mark_lastRes durchführen
            --       Info: die nachfolgende Aktion muss auf @Object_tbl_Id
            --              angewendet werden da ja von diesem Dataset die
            --              ProcessDaten verändert werden können
            Execute ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups @Object_tbl_Id
          end

       if @DeleteMethod = "M2_PDA"  or @DeleteMethod = "M3_DSS" or @DeleteMethod = "M4_IRS"
          begin
            -- Info dieser Vorgang muss bei den den Methoden M2_ M3_ und M4_ durchgefuehrt werden (05.01.2011 21:15 J.Fes)
            -- Step: delete Dataset All Process Data
            DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
             WHERE dbo.JobControl_ObjectsProcessData_tbl.ID in
             (SELECT
                dbo.JobControl_ObjectsProcessData_tbl.ID
              FROM
                dbo.Objects_tbl
                INNER JOIN dbo.JobControl_ObjectsProcessData_tbl ON (dbo.Objects_tbl.Id = dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID)
              WHERE
                dbo.Objects_tbl.Id = @Object_tbl_Id)
          end

       -- delte TC-Dataset Data  new 04.01.2011 J.Fes
       if @DeleteMethod = "M3_DSS"
          begin
            DECLARE @Msg varchar(255)
            -- Step: delete Dataset-DataRow
            DELETE FROM Objects_tbl WHERE Id=@Object_tbl_Id
            SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
            if @DebugPrintOn = 1
             begin
               EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '10-Step:M3_DSS del:', @Msg
             end
          end

       if @DeleteMethod = "M4_IRS"
          begin

           -- IrId ermitteln
           SELECT
             @IrId = dbo.Objects_Ir_Rel_tbl.IrId
           FROM
             dbo.Objects_tbl
             INNER JOIN dbo.Objects_Ir_Rel_tbl ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
           WHERE
             dbo.Objects_tbl.Id = @Object_tbl_Id

           -- Step: delete Relation from dbo.Objects_Ir_Rel_tbl
           DELETE FROM dbo.Objects_Ir_Rel_tbl
            WHERE dbo.Objects_Ir_Rel_tbl.ObjectId = @Object_tbl_Id

           -- Step: delete Dataset-DataRow
           DELETE FROM Objects_tbl WHERE Id=@Object_tbl_Id

           -- check ob noch ein weitere Datset auf IR vorhanden ist wenn nicht dann IR löschen new 05.02.2011 15:44  J.Fes
           -- Anzahl Referenzen zu IRID ermitteln
           select @I = count(*) from dbo.Objects_Ir_Rel_tbl
             where  Objects_Ir_Rel_tbl.IrId = @IrId ;
           if @DebugPrintOn = 1
              begin
                EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.1-loeschen @IrId:', @IrId
                EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.2-loeschen @I:', @I
              end

           -- wenn kein Element mehr vorhanden dann auch IR DS löschen
           if @I = 0
            begin
              if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.3-loeschen YES @IR:', @IrId
              DELETE FROM Objects_tbl WHERE Id=@IrId
            end
            else
            begin
              if @DebugPrintOn = 1 EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '05.3-loeschen NO:', @IrId
            end
          end

       if @DeleteMethod = "M5_ITM"
          begin
             -- löschen aller Daten eines ITEMS

             if (object_id('tempdb..#zzIt_tbl') is not null)  Drop table #zzIt_tbl
             CREATE TABLE #zzId_tbl(Id uniqueidentifier)
             -- insert into #zzId_tbl (Id) Values(@ObjectId)
             insert into #zzId_tbl execute Objects_tbl_IdsFromORootGet @Object_tbl_Id

              -- Step: delete Proces results
              DELETE FROM dbo.JobControl_ObjectsProcessData_tbl
                 WHERE dbo.JobControl_ObjectsProcessData_tbl.ID in (select Id from #zzId_tbl)

              -- Step: delete Relation from dbo.Objects_Ir_Rel_tbl
              DELETE FROM dbo.Objects_Ir_Rel_tbl
                 WHERE dbo.Objects_Ir_Rel_tbl.ObjectId in (select Id from #zzId_tbl)

              -- Step: delete Object Datasets
              DELETE FROM Objects_tbl WHERE Id in (select Id from #zzId_tbl)

          end


    end  -- Daten löschen via DataSet


EndeExit:

END
GO





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


Create Procedure Objects_tbl_GetId_IR_of_DS  @DSId nvarchar(128) ,
                                             @IrId nvarchar(128) Output
-- gibt die IrId zu einem DS zurück
As
BEGIN
  SELECT @IrId=IR.Id
  FROM
    dbo.Objects_tbl with (nolock)
    INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
    INNER JOIN dbo.Objects_tbl IR with (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = IR.Id)
  WHERE
    dbo.Objects_tbl.Id = @DSId
END
GO


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


Create Procedure Objects_tbl_GetId_UgMaster_of_DS @DSId nvarchar(128) ,
                                                  @DSUgMasterId nvarchar(128) Output
/* gibt die DsUgMasterId zu einem DS zurück  neu erstellt 20.06.2010 J.Fes */

As
BEGIN
 SELECT
   @DSUgMasterId=Id
 FROM
   Objects_tbl_GetId_UgMaster_of_DS_UDF(@DSId)
END
GO


Print '-- [Objects_tbl_GetId_UgMaster_of_DS_UDF] -------------------------------'
If Exists (select Name
           from sysobjects
            where Name = "Objects_tbl_GetId_UgMaster_of_DS_UDF")
DROP FUNCTION Objects_tbl_GetId_UgMaster_of_DS_UDF
GO

--- Gibt die ID des UgMaster datensatzes zu einem Dataset zurueck
CREATE FUNCTION Objects_tbl_GetId_UgMaster_of_DS_UDF(
                              @ObjectId  nvarchar(128)  -- ID des Datasets zu dem die UG Master Datensatz ID gesucht wird, 25.10.2017 changed to 128
        )
RETURNS TABLE AS
Return (
 SELECT -- Top 1
   Objects_tbl1.Id as Id
 FROM
   dbo.Objects_tbl WITH (nolock)
   INNER JOIN dbo.Objects_Ir_Rel_tbl with (nolock) ON (dbo.Objects_tbl.Id = dbo.Objects_Ir_Rel_tbl.ObjectId)
   INNER JOIN dbo.Objects_Ir_Rel_tbl Objects_Ir_Rel_tbl1 with (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Objects_Ir_Rel_tbl1.IrId)
   LEFT OUTER JOIN dbo.Objects_tbl Objects_tbl1 with (nolock) ON (Objects_Ir_Rel_tbl1.ObjectId = Objects_tbl1.Id)
 WHERE
   dbo.Objects_tbl.Id = @ObjectId AND
   Objects_tbl1.ObjectType = 'UGMASTER'
)
GO

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


-- #New:09.07.2017/J.Fes Edit Sync trigger
Create Procedure Objects_tbl_EditSyncTrigger  @ObjectId_p uniqueidentifier,
                                              @ObjectSyncTrigger int

As
BEGIN

  -- #LUp: 09.07.2017/J.Fes check if object is replica if yes then set Object trigger also to all objects with same @ObjectPuid
  -- #Doc: feststellen ob das Object ein replicat is
  DECLARE @ObjectFromSiteID  nvarchar(32),
          @ObjectOwningSiteID  nvarchar(32),
          @ObjectExportRecord  nvarchar(255),
          @ObjectPuid nvarchar(64)

  SELECT @ObjectFromSiteID = [ObjectFromSiteID],
         @ObjectOwningSiteID = [ObjectOwningSiteID],
         @ObjectExportRecord = [ObjectExportRecord],
         @ObjectPuid = [ObjectPuid]
         FROM Objects_tbl WHERE Id = @ObjectId_p

  if (@ObjectOwningSiteID <> @ObjectFromSiteID)
      OR (@ObjectExportRecord <> '')
  BEGIN
     -- #Doc: Object is replica object set Update ObjectSyncTrigger
     --       to all objects with same @ObjectPuid
     UPDATE Objects_tbl SET ObjectSyncTrigger = @ObjectSyncTrigger
            WHERE  ObjectPuid = @ObjectPuid
  END
  ELSE
  BEGIN
     -- #Doc: Object is not replica object set Update ObjectSyncTrigger
     --       to Object only
     UPDATE Objects_tbl SET ObjectSyncTrigger = @ObjectSyncTrigger
            WHERE Id = @ObjectId_p
  END


END
GO

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

CREATE PROCEDURE Objects_tbl_UpdateProcStateCol @ObjectId    nvarchar(128) , -- ID des zu bearbeitenden Datensatzes in Objects_tbl, 25.10.2017 changed to 128
                                                @ColumeName  nvarchar(32) , -- Name des Columes
                                                @ValueNew    nvarchar(MAX) -- neuer Wert
AS
BEGIN

     DECLARE @ExecuteSQLstr nvarchar(max)
     set @ExecuteSQLstr = 'UPDATE Objects_tbl SET [' + @ColumeName + ']=''' + @ValueNew + ''' where Objects_tbl.Id=''' + CONVERT(nvarchar(128), @ObjectId) + ''''
     execute sp_executesql @ExecuteSQLstr
end
Go

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



CREATE PROCEDURE Objects_tbl_UpdateColumeValue_by_ID @ObjectId              nvarchar(128) , -- ID des zu bearbeitenden Datensatzes in Objects_tbl, 25.10.2017 changed to 128
                                                     @ColumeName            nvarchar(32) , -- Name des Columes
                                                     @ValueNew              nvarchar(MAX), -- neuer Wert
                                                     @ObjectSpecialEditRule nvarchar(64),   -- erweiterte Edit funktionen
                                                     @ObjectSyncTrigger     int = null  -- #New: 09.07.2017 / activates Update
/*
   Description: Updaten die Daten des Columes in der Objects_tbl
   #LUp: 20.06.2010 18:38 J.Fes
*/

AS
BEGIN

  -- als erstes den Datensatz zur @ObjectId bearbeiten
  EXECUTE Objects_tbl_UpdateProcStateCol @ObjectId, @ColumeName, @ValueNew
  if @ObjectSyncTrigger IS NOT NULL
   BEGIN
     -- #LUp: 09.07.2017/J.Fes inserted into Objects_tbl_EditSyncTrigger
     exec Objects_tbl_EditSyncTrigger @ObjectId, @ObjectSyncTrigger
   END
  -- EXECUTE Objects_tbl_EditSyncTrigger @ObjectId, @ObjectSyncTrigger

--   DECLARE @DebugPrintOn bit
--   set @DebugPrintOn = 1
--   DECLARE @MyProcName nvarchar(32)
--   set @MyProcName='Objects_tbl_UpdateColumeValue_by_ID'
--
--
--   if @DebugPrintOn = 1
--   begin
--    -- Clear Debug Table
--    EXECUTE zzzzzDebugTbl_Empty
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@ObjectId',   @ObjectId
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@ColumeName', @ColumeName
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@ValueNew',   @ValueNew
--    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '04-@ObjectSpecialEditRule' , @ObjectSpecialEditRule
--   end
--

--     Dokumentation:
--     Public Enum ObjectSpecialEditRule_enum
--         NoSecRule
--         UgMaster_Of_Ir
--         SetPStateTo_IR_And_DS
--         SetPStateTo_All_DS_Off_UGMASTER
--         SetPStateTo_UgMaster_Off_DS
--     End Enum

  -- Option auch den Status der IR des Dataset ändern
  if @ObjectSpecialEditRule = 'SetPStateTo_IR_And_DS'
  begin
     DECLARE @IrId nvarchar(128)
     set @IrId = NULL
     EXECUTE Objects_tbl_GetId_IR_of_DS @ObjectId, @IrId Output
     -- EXECUTE zzzzzDebugTbl_AddDataSet 'Objects_tbl_UpdateColumeValue_by_ID' , N'02-@IrId:' , @IrId
     if @IrId is not NULL and @IrId <> @ObjectId
      begin
        -- wenn IR gefunden dann Updaten der Daten
        EXECUTE Objects_tbl_UpdateProcStateCol @IrId, @ColumeName, @ValueNew
      end
  end

  -- Option auch den Status des UgMaster Objektes zum Dataset ändern
  if @ObjectSpecialEditRule = 'SetPStateTo_UgMaster_Off_DS'
  begin
     -- Clear Debug Table
     DECLARE @DSUgMasterId nvarchar(128)
     set @DSUgMasterId = NULL
     EXECUTE Objects_tbl_GetId_UgMaster_of_DS @ObjectId, @DSUgMasterId Output
     if @DSUgMasterId is not NULL and @DSUgMasterId <> @ObjectId
      begin
       EXECUTE Objects_tbl_UpdateProcStateCol @DSUgMasterId, @ColumeName, @ValueNew
      end
      return
  end

END
GO
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

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



CREATE PROCEDURE Objects_tbl_Statistic
           (
           @ObjectFromSiteID   nvarchar(32) , -- Statistic for Site
           @IrsCount           int Output ,   -- Returns IrsCount
           @DsCount            int Output ,
           @NxMasterCount      int Output ,
           @NxNonMasterCount   int Output
           )
/* new 05.03.2011 19:24 J.Fes
   Returns Site Statistic
   */
AS
BEGIN

  DECLARE @DebugPrintOn bit
  DECLARE @I          integer
  DECLARE @MyProcName nvarchar(32)
  DECLARE @IrId       nvarchar(128)

  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_Statistic'

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

  Select @IrsCount=Count(*) From dbo.Objects_tbl WITH (nolock)
   where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
         and dbo.Objects_tbl.ObjectClass = 'IR'

--   Select @DsCount=Count(*) From dbo.Objects_tbl WITH (nolock)
--    where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
--          and dbo.Objects_tbl.ObjectClass = 'DS'
--
--   Select @NxMasterCount=Count(*) From dbo.Objects_tbl WITH (nolock)
--    where     dbo.Objects_tbl.ObjectFromSiteID = @ObjectFromSiteID
--          and dbo.Objects_tbl.ObjectClass = 'DS'
--          and dbo.Objects_tbl.ObjectType =  'UGMASTER'

  set @NxNonMasterCount = @DsCount - @NxMasterCount
  EndeExit:

END
GO



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

CREATE PROCEDURE Objects_tbl_DuplicatesCleanUp @Object_tbl_Id1 uniqueidentifier, -- ID1
                                               @Object_tbl_Id2 uniqueidentifier, -- ID2
                                               @Object_tbl_Id_delr uniqueidentifier = Null Output -- Inputs ID of DS to delete and Returns ID of deleted Dataset


/* new 12.04.2013 20:13 J.Fes
   bereinigt Duplicat Object Daten aus Objects_tbl:
   Folgende Regeln werden hierbei eingehalten:
   -- der juegere (ObjectLastModDate) Datensatz bleibt erhalten der aeltere wird geloescht
   -- evtl vorhandene Processdaten werden vom del nach keep uebertragen
   */

As
begin

  DECLARE @DebugPrintOn        bit
  DECLARE @MyProcName          nvarchar(32)
  DECLARE @Object_tbl_Id1_LastModDT  datetime
  DECLARE @Object_tbl_Id2_LastModDT  datetime
  DECLARE @Object_tbl_Id_keep  uniqueidentifier
  DECLARE @TempStr             nvarchar(32)


  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_DuplicatesCleanUp'

  if @DebugPrintOn = 1
  begin
   -- Clear Debug Table
   EXECUTE zzzzzDebugTbl_Empty
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '00-TEST' , 'TEST'
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '01-@Object_tbl_Id1' , @Object_tbl_Id1
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '02-@Object_tbl_Id2' , @Object_tbl_Id2
   EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '03-@Object_tbl_Id_delr' , @Object_tbl_Id_delr
  end

  if @Object_tbl_Id_delr IS NOT NULL
  BEGIN
    -- #Doc: select via @Object_tbl_Id_delr
    if  @Object_tbl_Id_delr = @Object_tbl_Id1
    BEGIN
      set @Object_tbl_Id_delr = @Object_tbl_Id1
      set @Object_tbl_Id_keep = @Object_tbl_Id2
    END
    ELSE
    BEGIN
      set @Object_tbl_Id_delr = @Object_tbl_Id2
      set @Object_tbl_Id_keep = @Object_tbl_Id1
    END
  END
  ELSE
  BEGIN
    -- Step: Autoselect to delete via newer older
    if @Object_tbl_Id1_LastModDT is null or @Object_tbl_Id2_LastModDT is null
    BEGIN
      -- 25.03.2015 12:27 J.Fes Update now it is also working if no Last Mod date given.
      -- Case Last Mod Date not avialble on at leased one Object
      set @Object_tbl_Id_keep = @Object_tbl_Id1
      set @Object_tbl_Id_delr = @Object_tbl_Id2
      if @Object_tbl_Id2_LastModDT is not null
      BEGIN
        set @Object_tbl_Id_keep = @Object_tbl_Id2
        set @Object_tbl_Id_delr = @Object_tbl_Id1
      END
    END
    ELSE
    BEGIN
      -- Case select data via Last Mod Date
      -- wenn @Objects_tbl_ID gegeben ist dann diese suchen lassen
      -- pruefen ob Object Datensatz in Objects_tbl vorhanden ist
      select @Object_tbl_Id1_LastModDT=ObjectLastModDate from Objects_tbl where ID = @Object_tbl_Id1
      select @Object_tbl_Id2_LastModDT=ObjectLastModDate from Objects_tbl where ID = @Object_tbl_Id2

      if @DebugPrintOn = 1
      BEGIN
        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '11-@Object_tbl_Id1_LastModDT', @Object_tbl_Id1_LastModDT
        EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '12-@Object_tbl_Id1_LastModDT', @Object_tbl_Id2_LastModDT
      END

      if @Object_tbl_Id1_LastModDT >= @Object_tbl_Id2_LastModDT
      BEGIN
        set @Object_tbl_Id_keep = @Object_tbl_Id1
        set @Object_tbl_Id_delr = @Object_tbl_Id2
      END
      ELSE
      BEGIN
        set @Object_tbl_Id_keep = @Object_tbl_Id2
        set @Object_tbl_Id_delr = @Object_tbl_Id1
      END
    END
  END


  if @DebugPrintOn = 1
  BEGIN
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_keep' , @Object_tbl_Id_keep
    select @TempStr=ObjectCliName from Objects_tbl where ID = @Object_tbl_Id_keep
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_keepCli' , @TempStr

    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '22-@Object_tbl_Id_delr' , @Object_tbl_Id_delr
    select @TempStr=ObjectCliName from Objects_tbl where ID = @Object_tbl_Id_delr
    EXECUTE zzzzzDebugTbl_AddDataSet @MyProcName , '21-@Object_tbl_Id_delCli' , @TempStr
  END

  -- Step: alle Process Daten von @Object_tbl_Id_delr nach an @Object_tbl_Id_keep uebertragen
  UPDATE JobControl_ObjectsProcessData_tbl
         set Objects_tbl_ID = @Object_tbl_Id_keep
         where  Objects_tbl_ID = @Object_tbl_Id_delr

  -- Step: del @Object_tbl_Id_delr
  EXECUTE Objects_tbl_DeleteData 'M3_DSS', @Object_tbl_Id_delr, 'DS', ''

  -- Step: ProcessStatus_Mark_LastRes
  EXECUTE ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups @Object_tbl_Id_keep

EndeExit:
END
GO

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


CREATE Procedure Objects_tbl_RemapStatus
                 @Objects_tbl_ID  nvarchar(36)
/* 25.03.2015 21:15/J.Fes ReMaps P.Status for Object with ID
   #Doc: Set dbo.Objects_tbl.ProcessProcStateCol01..20+ = from JobControl_ObjectsProcessData_tbl */
As
BEGIN
    declare @IdC uniqueidentifier,
            @ProcessProcStateCol nvarchar(64),
            @ProcessState nvarchar(32),
            @ExecuteSQLstr nvarchar(max)

    declare @tempTBL table
    (
      Id uniqueidentifier,
      ProcessProcStateCol nvarchar(64),
      ProcessState nvarchar(32)
    )

    INSERT @tempTBL
    SELECT
      JobControl_ObjectsProcessData_tbl.Id,
      JobControl_ObjectsProcessData_tbl.ProcessProcStateCol,
      JobControl_ObjectsProcessData_tbl.ProcessState
    FROM
      dbo.JobControl_ObjectsProcessData_tbl with (nolock)
    WHERE
      dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Objects_tbl_ID
    and dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1

    WHILE EXISTS (SELECT 1 FROM @tempTBL)
    BEGIN
      SELECT TOP 1 @IdC = Id FROM @tempTBL
      select @ProcessProcStateCol = ProcessProcStateCol ,
             @ProcessState = ProcessState
       from @tempTBL WHERE Id = @IdC

       set @ExecuteSQLstr = 'Update dbo.Objects_tbl set [' + @ProcessProcStateCol + '] = ' + '''' + @ProcessState + '''' + ' where dbo.Objects_tbl.Id=' + '''' + @Objects_tbl_ID + ''''
       -- print (@ExecuteSQLstr)
       exec (@ExecuteSQLstr)
       DELETE FROM @tempTBL WHERE Id = @IdC
    END
END
GO


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

CREATE PROCEDURE Objects_tbl_DuplicatesCleanUp2 @Object_tbl_IdRemove_p uniqueidentifier, -- ID2
                                                @Object_tbl_IdKeep_p uniqueidentifier,   -- ID1
                                                @ObjectClass nvarchar(10)


/* new 14.02.2015 12:00 J.Fes Cleans Duplicates via Merge */

As
begin

  DECLARE @DebugPrintOn        bit
  DECLARE @MyProcName          nvarchar(32)

  set @DebugPrintOn = 0
  set @MyProcName='Objects_tbl_DuplicatesCleanUp2'

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

  -- Step: move all Process Daten from: @Object_tbl_IdRemove_p to: @Object_tbl_IdKeep_p
  UPDATE JobControl_ObjectsProcessData_tbl
     SET Objects_tbl_ID = @Object_tbl_IdKeep_p
   WHERE Objects_tbl_ID = @Object_tbl_IdRemove_p

  -- Step: delete Relation from dbo.Objects_Ir_Rel_tbl
  DELETE FROM
    dbo.Objects_Ir_Rel_tbl
  WHERE
    dbo.Objects_Ir_Rel_tbl.IrId = @Object_tbl_IdRemove_p

  -- Step: delete Object Data
  DELETE FROM
    dbo.Objects_tbl
   WHERE
    Id=@Object_tbl_IdRemove_p

  -- Step: ProcessStatus_Mark_LastRes
  Execute ObjectsUpdate_ProcessStatus_Mark_LastRes_ForAll_ProcessGroups @Object_tbl_IdKeep_p

END
GO



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

/* new 08.01.2014 21:45 J.Fes
   Returns all IrPuid and DsPuid for TcSite and Ir
   @S = @ObjectFromSiteID
   @I = @IrPuid
   */
CREATE PROCEDURE Ot01 @S nvarchar(32),
                      @I nvarchar(64)


As
begin
  SELECT
    ID as Id,
    IrPuid as IrPuid,
    ObjectPuid AS DsPuid
   FROM dbo.DS_with_Irs_View WITH (nolock)
   WHERE
    dbo.DS_with_Irs_View.ObjectFromSiteID = @S and
    dbo.DS_with_Irs_View.IrPuid = @I
END
GO

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

/* new 15.08.2014 17:05 J.Fes
   Returns all IrPuid's for
   @S =  @ObjectFromSiteID
   @IR = @ItemRev
   */
CREATE PROCEDURE Ot02 @S nvarchar(32),
                      @IR nvarchar(32)


As
begin
  SELECT
    IrPuid as IrPuid,
    ID as Id
   FROM dbo.IRs_View WITH (nolock)
   WHERE
    dbo.Irs_View.ObjectFromSiteID = @S and
    dbo.Irs_View.IrName = @IR
END
GO


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

-- #New: 08.12.2016 / S.Gueth
-- #Updated ObjectName from nvarchar (32) to nvarchar (64), 18.01.2017, S.Gueth
-- #Updated ObjectName from nvarchar (64) to nvarchar (128), 25.10.2017, S.Gueth
Create Procedure Objects_tbl_AllObjectsViaObjectNameForFromSiteIds  @ObjectName        nvarchar(128),
                                                                    @ObjectClass       nvarchar(32),
                                                                    @ObjectFromSiteIDs nvarchar(max)
-- gibt Datensätze zum gesuchten ObjectName und diversen From Site Ids zurueck
As
BEGIN

    DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))   

    INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

    SELECT                       
      *
    FROM
      dbo.Objects_tbl
    WHERE
      dbo.Objects_tbl.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
      dbo.Objects_tbl.ObjectClass = @ObjectClass AND
      dbo.Objects_tbl.ObjectName = @ObjectName
end
go



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

-- #New: 08.12.2016 / S.Gueth
Create Procedure Objects_tbl_AllObjectsViaObjectPuidForFromSiteIds  @ObjectPuid        nvarchar(64),
                                                                    @ObjectClass       nvarchar(32),
                                                                    @ObjectFromSiteIDs nvarchar(max)
-- gibt Datensätze zur gesuchten ObjectPuid und diversen From Site Ids zurueck
As
BEGIN

    DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))   

    INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

    SELECT                       
      *
    FROM
      dbo.Objects_tbl
    WHERE
      dbo.Objects_tbl.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
      dbo.Objects_tbl.ObjectClass = @ObjectClass AND
      dbo.Objects_tbl.ObjectPuid = @ObjectPuid
end
go


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

-- #New: 08.12.2016 / S.Gueth
-- #Updated ItemId from nvarchar (32) to nvarchar (64), 18.01.2017, S.Gueth
Create Procedure Objects_tbl_AllItemRevObjectsViaItemIdForFromSiteIds  @ItemId            nvarchar(128),
                                                                       @ObjectFromSiteIDs nvarchar(max)
-- gibt alle ItemRev Datensätze zur gesuchten ItemId und diversen From Site Ids zurueck
As
BEGIN

    DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))   

    INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

    SELECT
         IR.*
      FROM
         dbo.Objects_tbl I
         INNER JOIN dbo.Objects_Ir_Rel_tbl I_IR_Rel ON (I.Id = I_IR_Rel.IrId)
         INNER JOIN dbo.Objects_tbl IR ON (I_IR_Rel.ObjectId = IR.Id)
      WHERE
         I.ObjectClass = 'I' AND
         I.ObjectName = @ItemId AND
         I.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
         IR.ObjectClass = 'IR'
end
go

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

-- #New: 08.12.2016 / S.Gueth
-- #Updated ItemId from nvarchar (32) to nvarchar (64), 18.01.2017, S.Gueth
Create Procedure Objects_tbl_AllDatasetObjectsViaItemIdForFromSiteIds  @ItemId            nvarchar(128),
                                                                       @ObjectFromSiteIDs nvarchar(max)
-- gibt alle Dataset Datensätze zur gesuchten ItemId und diversen From Site Ids zurueck
As
BEGIN

    DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))

    INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

    SELECT
       DS.*
    FROM
       dbo.Objects_tbl I
       INNER JOIN dbo.Objects_Ir_Rel_tbl I_IR_Rel ON (I.Id = I_IR_Rel.IrId)
       INNER JOIN dbo.Objects_tbl IR ON (I_IR_Rel.ObjectId = IR.Id)
       INNER JOIN dbo.Objects_Ir_Rel_tbl IR_DS_REL ON (IR.Id = IR_DS_REL.IrId)
       INNER JOIN dbo.Objects_tbl DS ON (IR_DS_REL.ObjectId = DS.Id)
    WHERE
       I.ObjectClass = 'I' AND
       I.ObjectName = @ItemId AND
       I.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
       IR.ObjectClass = 'IR' AND
       DS.ObjectClass = 'DS'
end
go

--------------------------------------------------------------------------------

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

Print '-- [Objects_tbl_IRObjectExists] ---------'
If Exists (select Name
           from sysobjects
           where Name = "Objects_tbl_IRObjectExists" and type = "P")
Drop Procedure Objects_tbl_IRObjectExists
GO
/*
New 22.08.2017 S. Gueth
Procedure that checks if an ir objects exists in the jobmanager database
*/
CREATE PROCEDURE Objects_tbl_IRObjectExists
                 @IRObjectName  nvarchar(max),    -- Name of ItemRevision 4711/A
                 @ObjectFromSiteIDs nvarchar(max) -- List of FromSiteIDs, seperated with ','

AS
BEGIN

DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))
INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

SELECT CASE WHEN EXISTS (
      SELECT 
        1
      FROM 
        dbo.Objects_tbl
      WHERE 
        dbo.Objects_tbl.ObjectName = @IRObjectName AND
        dbo.Objects_tbl.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
        dbo.Objects_tbl.ObjectClass = 'IR')
THEN CAST 
  (1 AS BIT)
ELSE CAST 
  (0 AS BIT)
END
END
GO
--------------------------------------------------------------------------------

Print '-- [ObjectsProcessData_tbl_ProcessResults] #New 29.01.2018/J.Fes --------'
If Exists (select Name
           from sysobjects
            where Name = "ObjectsProcessData_tbl_ProcessResults")
DROP PROCEDURE ObjectsProcessData_tbl_ProcessResults
GO


-- CREATE PROCEDURE ObjectsProcessData_tbl_ProcessResults(@ObjectFromSiteIDs nvarchar(255))
-- AS
-- BEGIN
--
-- DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))
-- INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')
--
-- SELECT TOP 100
--   Obj.Id As ObjectId,
--   Obj.ObjectClass,
--   Obj.ObjectFromSiteID,
--   Obj.ObjectCliName,
--   dbo.JobControl_ObjectsProcessData_tbl.ID AS ProcessId,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultLink,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultCode,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultMsg,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessPacketName,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessState,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeEnd,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessCMDLine,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessOnComputer,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessProcStateCol,
--   dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes,
--   dbo.JobControl_ObjectsProcessData_tbl.TaskScheduler_tbl_ID,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultHasWarning,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessPrgUsedVer,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessObjectSaveVersion,
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultExtendedData
-- FROM
--   dbo.Objects_tbl Obj
--   LEFT OUTER JOIN dbo.JobControl_ObjectsProcessData_tbl ON (Obj.Id = dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID)
-- WHERE
--   dbo.JobControl_ObjectsProcessData_tbl.ProcessResultLink IS NOT NULL
--   AND dbo.JobControl_ObjectsProcessData_tbl.ProcessResultLink <> ''
--   AND Obj.ObjectFromSiteID IN (SELECT siteId FROM @siteIdTable)
-- ORDER BY
--   Obj.ObjectFromSiteID,
--   Obj.ObjectClass
--
-- END
-- GO

CREATE PROCEDURE ObjectsProcessData_tbl_ProcessResults(@ObjectFromSiteIDs nvarchar(255))
AS
BEGIN

DECLARE @siteIdTable TABLE (siteId NVARCHAR(32))
INSERT INTO @siteIdTable (siteId) SELECT Value FROM dbo.SplitStringToTable_UDF(@ObjectFromSiteIDs, N',')

SELECT -- TOP 100
  Obj.Id As ObjectId,
  Obj.ObjectClass As ObjectClass,
  Obj.ObjectFromSiteID As ObjectFromSiteID,
  Obj.ObjectCliName As ObjectCliName,
  PData.ID AS ProcessId,
  PData.ProcessResultLink As ProcessResultLink,
  PData.ProcessResultCode As ProcessResultCode,
  PData.ProcessResultMsg As ProcessResultMsg,
  PData.ProcessPacketName As ProcessPacketName,
  PData.ProcessState As ProcessState,
  PData.ProcessTimeStart As ProcessTimeStart,
  PData.ProcessTimeEnd As ProcessTimeEnd,
  PData.ProcessCMDLine As ProcessCMDLine,
  PData.ProcessOnComputer As ProcessOnComputer,
  PData.ProcessProcStateCol As ProcessProcStateCol,
  PData.Row_isLastRes As Row_isLastRes,
  PData.TaskScheduler_tbl_ID As TaskScheduler_tbl_ID,
  PData.ProcessResultHasWarning As ProcessResultHasWarning,
  PData.ProcessPrgUsedVer As ProcessPrgUsedVer,
  PData.ProcessObjectSaveVersion As ProcessObjectSaveVersion,
  PData.ProcessResultExtendedData As ProcessResultExtendedData
FROM
  dbo.Objects_tbl Obj
  LEFT OUTER JOIN dbo.JobControl_ObjectsProcessData_tbl Pdata ON (Obj.Id = PData.Objects_tbl_ID)
WHERE
  PData.ProcessResultLink IS NOT NULL
  AND PData.ProcessResultLink <> ''
  AND Obj.ObjectFromSiteID IN (SELECT siteId FROM @siteIdTable)
ORDER BY
  Obj.ObjectFromSiteID,
  Obj.ObjectClass

END
GO

---------------------------------------------------------------------------------------
-- Print '-- [JobMgr_30_ApplProceduresObjects_test] -------------------------------------------'
-- If Exists (select Name from sysobjects where Name = "JobMgr_30_ApplProceduresObjects_test" and type = "P")
--                                       Drop Procedure JobMgr_30_ApplProceduresObjects_test
-- GO
--
-- Create Procedure JobMgr_30_ApplProceduresObjects_test
-- As
-- BEGIN
--  DECLARE @ProcStateCol01Value nvarchar(10) ,
--          @ObjectId  uniqueidentifier
--
--          set @ObjectId = '0a239703-bfe9-4cc4-b476-b8de1638a1df'
--          execute dbo.Objects_tbl_Object_Get_ProcessStatus_UDF @ObjectId, 'ProcStateCol02', @ProcStateCol01Value output
--          PRINT ('@ProcStateCol01Value:' + @ProcStateCol01Value)
--          set @ObjectId = '1e3ee47e-a8eb-4834-8e6f-8ade39130c1c'
--          execute dbo.Objects_tbl_Object_Get_ProcessStatus_UDF @ObjectId, 'ProcStateColRf', @ProcStateCol01Value output
--          PRINT ('@ProcStateCol01Value:' + @ProcStateCol01Value)
-- end
-- GO
---------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------

-- Print ''
-- Print ''
-- Print ''
-- Print '---------------------------------------------------------------------'
-- Print ' Test Procedures ObjectsUpdate_ProcessStatus_Mark_LastRes'
-- Print '----------------------------------------------------'
-- GO
--

/*
 Declare   @ObjectFromSiteID nvarchar(32)
 Declare   @IrsCount         int
 Declare   @DsCount          int
 Declare   @NxMasterCount    int
 Declare   @NxNonMasterCount int


 set @ObjectFromSiteID='tc83'
 EXECUTE Objects_tbl_Statistic @ObjectFromSiteID, @IrsCount Output, @DSCount Output , @NxMasterCount Output , @NxNonMasterCount Output
 Print ""

 Print "SiteStatistic...: [" + @ObjectFromSiteID + "]"
 Print "IrsCount........: [" + convert(nvarchar,@IrsCount) + "]"
 Print "DsCount.........: [" + convert(nvarchar,@DsCount)  + "]"
 Print "NxMasterCount...: [" + convert(nvarchar,@NxMasterCount)  + "]"
 Print "NxNonMasterCount: [" + convert(nvarchar,@NxNonMasterCount)  + "]"
*/

--   Print ""  Print ""
--   Print "#Debug 12.04.2013 21:06 / J.Fes "
--   Print "#Debug Objects_tbl_DuplicatesCleanU"
  -- BG-Einzelteil-01/A	BG-Einzelteil-01-A	05812fe2-d2d9-4721-951d-dccf26ce0d8e	30.08.2011 12:46
  -- BG-Einzelteil-03/A	BG-Einzelteil-03-A	c36c86e0-7761-4bce-ae28-1b9c397cc55a	30.08.2011 12:46
  -- BG-Einzelteil-04/A	BG-Einzelteil-04-A	80a99333-c3a9-4173-bc0d-bde9a7e072af	30.08.2011 12:46
  -- EXECUTE  Objects_tbl_DuplicatesCleanUp 'eb92367f-2636-4d99-ad2c-f18241d0cbb3', '6a03c1ab-1639-41b7-9f09-14244b2ba392'

--  EXECUTE  Objects_tbl_RemapStatus 'd5998176-56b0-4fca-b8ab-c210f7d34f5f'

-- execute JobMgr_30_ApplProceduresObjects_test
GO
ende:

Print ''
Print '-------------------------------------------------------------------------'
Print ' Procedures imported to #DB_N#'
Print '-------------------------------------------------------------------------'
-- EXECUTE zzzzzDebugTbl_Empty
GO

