Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database: #DB_N#'
Print ' Procedures    : JobControl'
Print ' Creation Date : 12.02.2010'
Print ' Autor         : J.Feuerstein'
Print ' '
Print '-------------------------------------------------------------------------'
GO

use [#DB_N#]
GO

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


/* Neu: 12.02.2010 15:33 / J.Fes
   Description: Sucht in JobControl_ObjectsProcessData_tbl für: @Object_tbl_ID nach unbearbeiteten JobPlaner eintrag
                 siehe hierzu .Net V.02-VB.NET\JobServer.DataSource\JobServer_DataSource.vb:JobControl_ObjectsProcess_tbl_JobPlaner_ObjectsProcessData
   Letze Anpassung: 04.04.2012 20:27  WHERE
    dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1 -- AND
   order by dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart DESC
*/
CREATE PROCEDURE JobControl_ObjectsProcess_tbl_JobPlaner_ObjectsProcessData
                 @Object_tbl_ID  nvarchar(64)   -- ID des Datensatzes in dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID

AS
BEGIN

 SELECT TOP 1
  -- new 15.10.2013/J.Fes  use to get IR Data
  Ir.ObjectPuid AS IrPuid,
  Ir.ObjectName AS IrName,
  dbo.JobControl_ObjectsProcessData_tbl.*,
  dbo.Objects_tbl.*

FROM
  dbo.JobControl_ObjectsProcessData_tbl with (nolock)
      INNER JOIN dbo.Objects_tbl WITH (nolock) ON (dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = dbo.Objects_tbl.Id),
  -- new 15.10.2013/J.Fes use to get IR Data
  dbo.Objects_Ir_Rel_tbl with (nolock)
      LEFT OUTER JOIN dbo.Objects_tbl Ir WITH (nolock) ON (dbo.Objects_Ir_Rel_tbl.IrId = Ir.Id)
WHERE
      dbo.JobControl_ObjectsProcessData_tbl.Objects_tbl_ID = @Object_tbl_ID
  AND dbo.JobControl_ObjectsProcessData_tbl.Row_isLastRes = 1
ORDER BY
  dbo.JobControl_ObjectsProcessData_tbl.ProcessTimeStart DESC

END
GO


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

/*
New 21.07.2017 S. Gueth
Procedure that gets the count of how offent a ProcessPacket is refferenced in JobManager database
*/
CREATE PROCEDURE JobControl_ObjectsProcessData_tbl_CountOfProcessPacketNameDBReference
                 @ProcessPacketName  nvarchar(64)   -- Name of the process packet

AS
BEGIN
SELECT
  count(*) as ProcessPacketRefCount
FROM 
  dbo.JobControl_ObjectsProcessData_tbl
WHERE 
  dbo.JobControl_ObjectsProcessData_tbl.ProcessPacketName = @ProcessPacketName
END
GO
--------------------------------------------------------------------------------

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

Print '-- [JobControl_ObjectsProcessData_tbl_CBResultFileHasDBRef] ---------'
If Exists (select Name
           from sysobjects
           where Name = "JobControl_ObjectsProcessData_tbl_CBResultFileHasDBRef")
Drop Procedure JobControl_ObjectsProcessData_tbl_CBResultFileHasDBRef
GO
/*
New 03.08.2017 S. Gueth
LUp 29.03.2019 S. Gueth, changed sql sattement for multiple result links 
Procedure that gets the count of how much a CBXmlFile is refferenced in JobManager database (as a process result link)
*/
CREATE PROCEDURE JobControl_ObjectsProcessData_tbl_CBResultFileHasDBRef
                 @CBResultLinks  nvarchar(max),   -- CheckBox result links, seperated with ','
                 @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',')

DECLARE @resultLinks TABLE (resLink NVARCHAR(max))
INSERT INTO @resultLinks (resLink) SELECT Value FROM dbo.SplitStringToTable_UDF(@CBResultLinks, N',')

SELECT CASE WHEN EXISTS(
        SELECT 
            1
        FROM 
            dbo.JobControl_ObjectsProcessData_tbl AS PD
            INNER JOIN dbo.Objects_tbl AS Obj ON (PD.Objects_tbl_ID = Obj.Id)
        WHERE 
            PD.Objects_tbl_ID = Obj.Id AND
            Obj.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable) AND
            PD.ProcessResultLink IN(SELECT resLink FROM @resultLinks))
THEN CAST
  (1 AS BIT)
ELSE CAST
  (0 AS BIT)
END
END
GO


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

/*
New 21.07.2017 S. Gueth
Procedure that check if the last cb compair result was ok
*/
CREATE PROCEDURE JobControl_ObjectsProcessData_tbl_CBCompairLastResultIsOK
                 @CBDifFile  nvarchar(max),           -- Path and Name of the CB dif file
                 @ProcessProcStateCol nvarchar(32),   -- ProcessColumn Name
                 @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 (
				SELECT objProcData.ProcessResultCode, objProcData.ProcessResultHasWarning
				FROM JobControl_ObjectsProcessData_tbl objProcData 
				INNER JOIN Objects_tbl obj on objProcData.Objects_tbl_ID = obj.Id  
				WHERE 
            (objProcData.Row_isLastRes = 1 or objProcData.Row_isLastRes = NULL) and 
            objProcData.ProcessResultLink like '%' + @CBDifFile + '%' and 
					  objProcData.ProcessProcStateCol = @ProcessProcStateCol  and 
					  obj.ObjectFromSiteID IN(SELECT siteId FROM @siteIdTable)
					  ) AS Q
		WHERE 
              Q.ProcessResultCode = '0'  and    
             (Q.ProcessResultHasWarning = 0 or Q.ProcessResultHasWarning = NULL)
        )
THEN CAST
  (1 AS BIT)
ELSE CAST
  (0 AS BIT)
END
END
GO
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------


-- Print ''
-- Print ''
-- Print ''
-- Print '----------------------------------------------------'
-- Print ' Test Procedures ObjectsUpdate_ProcessStatus_Mark_LastRes'
-- Print '----------------------------------------------------'
-- GO
-- im9t
-- Execute JobControl_ObjectsProcess_tbl_JobPlaner_ObjectsProcessData '5d7c3cc4-227d-4333-8615-dd40f70b909e'
-- go
-- goto ende
ende:

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