Print ''
Print '-------------------------------------------------------------------------'
Print ' using Database...........: #DB_N#'
Print ' Executing SQL Script.....: JobMgr_30_ApplProceduresReports04_InWork.sql Script'
Print ' #New.....................: 01.11.2019/A.Stein'
Print ' #LUp.....................: 01.11.2019/A.Stein'
Print '-------------------------------------------------------------------------'

use [#DB_N#]

Print '---  R E P O R T S - 04 In Work  ----------------------------------------'


-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns the latest result of each object until a specified date
-- =============================================
Create FUNCTION dbo.LatestProcessResultsUntilDate
(	
  @ProcessTimeEnd DATETIME,
  @ProcessProcStateCol NVARCHAR(32),
  @ObjectClass NVARCHAR(10) = NULL,
  @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
)
RETURNS
@res TABLE 
(
	-- Add the column definitions for the TABLE variable here
	Objects_tbl_ID UNIQUEIDENTIFIER, 
	ProcessTimeEnd DATETIME,
	ProcessState NVARCHAR(34)
)
AS
BEGIN
	-- needed Index on dbo.JobControl_ObjectsProcessData_tbl: ProcessStateCol, ProcessTimeEnd, Objects_tbl_ID

	-- default ObjectClass
	SET @objectClass = ISNULL(@ObjectClass, 'I')

	-- split group ids
	DECLARE @groupIds TABLE (groupId UNIQUEIDENTIFIER)
	DECLARE @anyGroupIds BIT = 0

	INSERT INTO @groupIds(groupId)
	  SELECT cast(Value as UNIQUEIDENTIFIER)
	  FROM dbo.SplitStringToTable_UDF(@ObjectGroupGuidList, ';')
	  WHERE Value LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

	IF EXISTS (SELECT 1 FROM @groupIds)
	  set @anyGroupIds = 1

	INSERT INTO @res (Objects_tbl_ID, ProcessTimeEnd, ProcessState)
	SELECT OPD.Objects_tbl_ID, OPD.ProcessTimeEnd, OPD.ProcessState
	FROM dbo.JobControl_ObjectsProcessData_tbl OPD
	WHERE 
      (
	     -- entweder alle Objekte, oder die einer Gruppe
		 @anyGroupIds = 0 
		 OR
		 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId FROM @groupIds) AND ObjectId=OPD.Objects_tbl_ID)
	  )
	  
	  AND

	  -- ObjectClass
	  EXISTS (SELECT 1 FROM dbo.Objects_tbl OBJ WHERE OBJ.Id=OPD.Objects_tbl_ID AND OBJ.ObjectClass=@ObjectClass)
	  
	  AND

	  -- ProcStateCol
	  OPD.ProcessProcStateCol = @ProcessProcStateCol

	  AND
	  
	  -- Zeitbereich einschränken
	  OPD.ProcessTimeEnd <= @ProcessTimeEnd
	  
	  AND

	  -- nur zeitlich letztes gültige Ergebnis
	  OPD.ProcessTimeEnd
		= (
		SELECT MAX(OPD2.ProcessTimeEnd)
		FROM dbo.JobControl_ObjectsProcessData_tbl OPD2
		WHERE OPD2.Objects_tbl_ID = OPD.Objects_tbl_ID 
			AND OPD2.ProcessTimeEnd <= @ProcessTimeEnd AND OPD2.ProcessProcStateCol=@ProcessProcStateCol
			AND OPD2.ProcessTimeEnd IS NOT NULL
		)

	RETURN

END    
GO

Print '-- [LatestProcessResultsUntilDateGroupedByState] ------------------------'
If Exists (select Name from sysobjects
        where Name = 'LatestProcessResultsUntilDateGroupedByState'
        and type ='IF') Drop Function LatestProcessResultsUntilDateGroupedByState
GO
       
-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns the counts of each latest result state over all objects until a specified date
-- =============================================
CREATE FUNCTION dbo.LatestProcessResultsUntilDateGroupedByState 
(	
  @ProcessTimeEnd DATETIME,
  @ProcessProcStateCol NVARCHAR(32),
  @ObjectClass NVARCHAR(10) = NULL,
  @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT 
		SUM(CASE WHEN Q.CleanedStatus = N'D' THEN 1 ELSE 0 END) AS CountD,
		SUM(CASE WHEN Q.CleanedStatus = N'D.OK' THEN 1 ELSE 0 END) AS CountOK,
		SUM(CASE WHEN Q.CleanedStatus = N'D.WRN' THEN 1 ELSE 0 END) AS CountWRN,
		SUM(CASE WHEN Q.CleanedStatus = N'D.ERR' THEN 1 ELSE 0 END) AS CountERR,
		SUM(CASE WHEN Q.CleanedStatus = N'X' THEN 1 ELSE 0 END) AS CountX,
		SUM(CASE WHEN Q.CleanedStatus = N'T' THEN 1 ELSE 0 END) AS CountT,
		SUM(CASE WHEN Q.CleanedStatus = N'R' THEN 1 ELSE 0 END) AS CountR,
		SUM(CASE WHEN Q.CleanedStatus = N'P' THEN 1 ELSE 0 END) AS CountP,
		SUM(CASE WHEN Q.CleanedStatus = N'UNKNOWN' THEN 1 ELSE 0 END) AS CountUNKN,
		SUM(1) AS CountAllProcessed

	FROM (
		SELECT 
		CASE WHEN ProcessState LIKE N'D%.OK' THEN 'D.OK' ELSE
		CASE WHEN ProcessState LIKE N'D%.WRN' THEN 'D.WRN' ELSE
		CASE WHEN ProcessState LIKE N'D%.ERR' THEN 'D.ERR' ELSE
		CASE WHEN ProcessState LIKE N'[XTRPD]%' THEN SUBSTRING(ProcessState,1,1) ELSE
		     'UNKNOWN' END END END END AS CleanedStatus
		
		FROM dbo.LatestProcessResultsUntilDate(@ProcessTimeEnd, @ProcessProcStateCol, @ObjectClass, @ObjectGroupGuidList)
	) Q
)
GO




Print '-- [LatestProcessResultsUntilDate] -------------------------------------------'
If Exists (select Name from sysobjects
        where Name = 'LatestProcessResultsUntilDate'
        and type = 'TF') Drop Function LatestProcessResultsUntilDate
GO


   

-- #New 01.11.2019 09:18:06
-- =============================================
-- Author:		Alexander Becker
-- Create date: 31.10.2019
-- Description:	Returns an intervalled report of the counts of each latest result state over all objects until a specified date
-- =============================================


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

CREATE PROCEDURE dbo.Report_903_ObjectsProcessDataCummulatedResultCounts
	-- Add the parameters for the stored procedure here
	@ProcessTimeEnd DATETIME,
	@ProcessProcStateCol NVARCHAR(32),
	@interval NVARCHAR(100) = 'daily', -- daily, weekly, monthly, querterly, yearly
	@ObjectClass NVARCHAR(10),
    @ObjectGroupGuidList NVARCHAR(MAX) = NULL -- sample: 'CA644377-F747-4F67-B78D-01C2E8623D0E;825B6C1D-D516-4CAB-8F1E-0444D1D81556;...'
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- split group ids
	DECLARE @groupIds TABLE (groupId UNIQUEIDENTIFIER)
	DECLARE @anyGroupIds BIT = 0

	INSERT INTO @groupIds(groupId)
	  SELECT cast(Value as UNIQUEIDENTIFIER)
	  FROM dbo.SplitStringToTable_UDF(@ObjectGroupGuidList, ';')
	  WHERE Value LIKE '[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F]-[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F]'

	IF EXISTS (SELECT 1 FROM @groupIds)
	  set @anyGroupIds = 1

	-- get first date from process results
	DECLARE @firstResultDate DATETIME
	SELECT @firstResultDate=MIN(OPD.ProcessTimeEnd)
	FROM dbo.JobControl_ObjectsProcessData_tbl OPD
	WHERE OPD.ProcessTimeEnd IS NOT NULL AND
	      OPD.ProcessProcStateCol=@ProcessProcStateCol AND
		  EXISTS (SELECT 1 FROM dbo.Objects_tbl OBJ WHERE OBJ.Id=OPD.Objects_tbl_ID AND OBJ.ObjectClass=@ObjectClass) AND
		  (
			 -- entweder alle Objekte, oder die einer Gruppe
			 @anyGroupIds = 0
			 OR
			 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId from @groupIds) AND ObjectId=OPD.Objects_tbl_ID)
		  )
	DECLARE @intervalDate DATETIME
	DECLARE @intervalLabel NVARCHAR(100)

	-- compute object count
	DECLARE @objectCount INT
	SELECT @objectCount=COUNT(1)
	FROM dbo.Objects_tbl OBJ
	WHERE OBJ.ObjectClass=@ObjectClass AND
		  (
			 -- entweder alle Objekte, oder die einer Gruppe
			 @anyGroupIds = 0
			 OR
			 EXISTS (SELECT 1 FROM dbo.Objects_ObjectGroups_Rel_tbl WHERE @anyGroupIds=1 AND ObjectGroupId IN (SELECT groupId from @groupIds) AND ObjectId=OBJ.Id)
		  )

	-- result table
	DECLARE @res TABLE (intervalDate DATETIME, intervalLabel NVARCHAR(100), 
	                    AllObjectsCount INT, NotProcessedCount INT, 
						CountD INT, CountOK INT, CountWRN INT, CountERR INT, CountX INT, CountT INT, CountR INT, CountP INT, CountUNKN INT)

	-- computer start date
	IF @interval = 'daily'
		SET @intervalDate = @firstResultDate;
	IF @interval = 'weekly'
		SET @intervalDate = DATEADD(week, DATEDIFF(week, 0, @firstResultDate), 0);
	IF @interval = 'monthly'
		SET @intervalDate = DATEADD(month, DATEDIFF(month, 0, @firstResultDate), 0)
	IF @interval = 'quarterly'
		SET @intervalDate = DATEADD(quarter, DATEDIFF(quarter, 0, @firstResultDate), 0)
	IF @interval = 'yearly'
		SET @intervalDate = DATEADD(year, DATEDIFF(year, 0, @firstResultDate), 0)

	-- täglich
	WHILE @intervalDate <= @ProcessTimeEnd
	BEGIN
		
		-- format interval label
		IF @interval = 'daily'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM-dd')
		IF @interval = 'weekly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy') + '-CW' + RIGHT('00' + CAST(DATEPART(week, @intervalDate) AS VARCHAR(10)),2)
		IF @interval = 'monthly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM-')
		IF @interval = 'quarterly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy-MM')
		IF @interval = 'yearly'
			SET @intervalLabel = FORMAT(@intervalDate, 'yyyy')

		-- add interval data
		INSERT INTO @res (intervalDate, intervalLabel, AllObjectsCount, NotProcessedCount, CountD, CountOK, CountWRN, CountERR, CountX, CountT, CountR, CountP, CountUNKN)
		SELECT @intervalDate, @intervalLabel, @objectCount, @objectCount-CountAllProcessed, CountD, CountOK, CountWRN, CountERR, CountX, CountT, CountR, CountP, CountUNKN
		FROM dbo.LatestProcessResultsUntilDateGroupedByState(@intervalDate, @ProcessProcStateCol, @ObjectClass, @ObjectGroupGuidList)

		-- increase interval date
		IF @interval = 'daily'
			SET @intervalDate = DATEADD(day, 1, @intervalDate)
		IF @interval = 'weekly'
			SET @intervalDate = DATEADD(week, 1, @intervalDate)
		IF @interval = 'monthly'
			SET @intervalDate = DATEADD(month, 1, @intervalDate)
		IF @interval = 'quarterly'
			SET @intervalDate = DATEADD(quarter, 1, @intervalDate)
		IF @interval = 'yearly'
			SET @intervalDate = DATEADD(year, 1, @intervalDate)

	END
	
	SELECT * FROM @res

END
GO








/****** Object:  Index [ProcCol_TimeEnd_ObjId]    Script Date: 31.10.2019 17:19:41 ******/

CREATE NONCLUSTERED INDEX [ProcCol_TimeEnd_ObjId] ON [dbo].[JobControl_ObjectsProcessData_tbl]
(
	[ProcessProcStateCol] ASC,
	[ProcessTimeEnd] ASC,
	[Objects_tbl_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO




/****** Object:  Index [ProcCol_TimeEnd_ObjId]    Script Date: 31.10.2019 17:19:41 ******/

/*
CREATE NONCLUSTERED INDEX [ProcCol_TimeEnd_ObjId] ON [dbo].[JobControl_ObjectsProcessData_tbl]
(
	[ProcessProcStateCol] ASC,
	[ProcessTimeEnd] ASC,
	[Objects_tbl_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

*/


Print ''
Print '---------------------------------------------------------------------------------------'
Print ' R E P O R T S imported to #DB_N#'
Print '---------------------------------------------------------------------------------------'
GO
