USE [SISIMPU]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--CREATE VIEW [Transaction].[View_Synchronization]
--AS
SELECT 
	D.RegulationId, 
	D.RegulationRegulationTypeId, 
	D.RegulationConditionId, 
	D.RegulationOrganizationId, 
	D.RegulationIsBoardSeminar, 
	D.RegulationName, 
	D.RegulationDescription, 
	D.RegulationCreateBy, 
	D.RegulationCreateDate, 
	D.RegulationUpdateBy, 
	D.RegulationUpdateDate, 
	D.RegulationStatus, 
	D.RegulationLogCode, 
	D.RegulationStep, 
	D.RegulationStepStatus, 
	D.RegulationCancelBy, 
	D.RegulationCancelDate, 
	D.RegulationReason, 
	D.RegulationCancelStep, 
	D.RegulationCancelStepName, 
	D.RegulationTypeId, 
	D.RegulationTypeIsBoardSeminar, 
	D.RegulationTypeCode, 
	D.RegulationTypeName, 
	D.RegulationTypeDescription, 
	D.OrganizationId, 
	D.OrganizationParentId, 
	D.OrganizationSectorId, 
	D.OrganizationCode, 
	D.OrganizationName, 
	D.SectorCode, 
	D.SectorName, 
	D.OrganizationIsPPM, 
	D.ConditionId, 
	D.ConditionCode, 
	D.ConditionName, 
	D.ConditionDescription, 
	S.SynchronizationId,
	S.SynchronizationRegulationId,
	S.SynchronizationRdkId,
	S.SynchronizationRequestNDNomor,
	S.SynchronizationRequestNDDate,
	S.SynchronizationRequestNDDocumentId,
	S.SynchronizationRequestDate,
	S.SynchronizationResponseNDNomor,
	S.SynchronizationResponseNDDate,
	S.SynchronizationResponseNDDocumentId,
	S.SynchronizationResponseDate,
	S.SynchronizationCreateBy,
	S.SynchronizationCreateDate,
	S.SynchronizationUpdateBy,
	S.SynchronizationUpdateDate,

	--R.RdkDate,
	CASE
		WHEN S.SynchronizationId	IS NULL THEN	-- jika belum ada pengharmonisasian
										CASE
											WHEN D.ConditionCode = '01' THEN	--Normal
												[dbo].[FN_AddBusinessDays] (R.RdkDate, 3)	-- belum menyertakan libur nasional
											WHEN D.ConditionCode = '02' THEN	--Mendesak
												[dbo].[FN_AddBusinessDays] (R.RdkDate, 2)	-- belum menyertakan libur nasional
										END
		-- WHEN harmonization request
		ELSE NULL
	END AS SynchronizationRequestDueDate,
	CASE
		WHEN S.SynchronizationId	IS NULL THEN	-- jika belum ada pengharmonisasian
										CASE
											WHEN D.ConditionCode = '01' THEN	--Normal
												CASE
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) < 0 THEN 'Overdue'
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) = 0 THEN 'Last day'
													ELSE CAST(3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) AS varchar(8))
												END
											WHEN D.ConditionCode = '02' THEN	--Mendesak
												CASE
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) < 0 THEN 'Overdue'
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) = 0 THEN 'Last day'
													ELSE CAST(2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) AS varchar(8))
												END
										END
		-- WHEN harmonization request
		ELSE NULL
	END AS SynchronizationRequestDueDateNotes,

	R.RdkId, 
	R.RdkRegulationId, 
	R.RdkHarmonizationId, 
	R.RdkDocumentId, 
	R.RdkNomorKRDK, 
	R.RdkDate, 
	R.RdkType, 
	R.RdkCreateBy, 
	R.RdkCreateDate, 
	R.RdkUpdateBy, 
	R.RdkUpdateDate, 
	R.RdkApproveBy, 
	R.RdkApproveDate, 
	R.RdkStatus,
	CASE
		WHEN D.RegulationStep < 8 OR D.RegulationStep = 8 AND D.RegulationStepStatus = 0 OR D.RegulationStep = 19 AND D.RegulationStepStatus = 0
		THEN CASE
				WHEN R.RdkType = 1
				THEN [Transaction].FN_GetSLA(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)
				ELSE [Transaction].FN_GetSLA(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)
			END
		ELSE CASE
				WHEN R.RdkType = 1
				THEN CONVERT(VARCHAR, CONVERT(DATETIME, [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)), 103)
				ELSE CONVERT(VARCHAR, CONVERT(DATETIME, [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)), 103)
			END
	END AS BatasAkhirNDPermintaanPencetakanHTML,
	CASE
		WHEN R.RdkType = 1
		THEN [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Pencetakan', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)
		ELSE [Transaction].FN_GetSLADate(R.RdkDate, 'Batas Akhir Penyampaian ND Harmonisasi Kembali', D.RegulationRegulationTypeId, D.RegulationConditionId, 0)
	END AS BatasAkhirNDPermintaanPencetakanDate,
	CASE
		WHEN R.RdkType = 1
		THEN 'RDK Tanpa Perubahan Rancangan'
		WHEN R.RdkType = 2
		THEN 'RDK Dengan Perubahan Rancangan'
		WHEN R.RdkType = 3
		THEN 'Perubahan Rancangan Dengan RDK Berulang'
		ELSE NULL
	END AS RdkTypeName, 
	[Transaction].FN_GetTahapanDraft(D.RegulationStep) AS RegulationStepName, 
	[Transaction].FN_GetStatusDraft(D.RegulationStep, D.RegulationStepStatus) AS RegulationStepStatusName, 
	[Transaction].FN_GetDokumenPath(1, 7|18, D.RegulationId) AS DraftPeraturanDocumentFileName
FROM 
	[Transaction].View_RegulationDraft AS D
	LEFT OUTER JOIN
	[Transaction].Synchronizations AS S ON
		S.SynchronizationRegulationId = D.RegulationId
	LEFT OUTER JOIN
	[Transaction].Rdk AS R ON
		R.RdkRegulationId = D.RegulationId
		AND R.RdkStatus = 1
WHERE
	--D.RegulationStatus = 1
	--AND D.RegulationStep >= 8
	--OR D.RegulationStep IN (6, 7, 17, 18)
	--AND D.RegulationStepStatus IN (11, 12)

	(D.RegulationStatus = 1) 
	AND (
			D.RegulationStep >= 8 
			OR D.RegulationStep >= 19
		) 
	AND (D.RegulationStepStatus >= 0) 
	AND (D.RegulationStep NOT IN (9, 10, 11)) 
	OR (D.RegulationStep <= 4) 
	AND (D.RegulationTypeId = 4)

--GO

--select * from [Transaction].Rdk
--select * from [Transaction].Synchronizations
--select * from [Master].DayOff

--		(DATEDIFF(dd, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0), EOMONTH(@tanggal)) + 1) 
--		- (DATEDIFF(wk, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0), EOMONTH(@tanggal)) * 2)
--		- (CASE WHEN DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0)) = 'Sunday' THEN 1 ELSE 0 END)
--		- (CASE WHEN DATENAME(dw, EOMONTH(@tanggal)) = 'Saturday' THEN 1 ELSE 0 END)
--		- (SELECT COUNT(*) FROM dbo.SPG_HARI_LIBUR WHERE TANGGAL_HARI_LIBUR BETWEEN DATEADD(month, DATEDIFF(month, 0, @tanggal), 0) AND EOMONTH(@tanggal) AND NASIONAL = '1')

--select 
--	DATEDIFF(month, 0, GETDATE())
--	DATEADD(month, DATEDIFF(month, 0, GETDATE())
--	DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))
--	CASE 
--		WHEN DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0)) = 'Sunday' THEN 1 
--		ELSE 0 
--	END

--DECLARE @StartDate DATETIME
--DECLARE @EndDate DATETIME
--SET @StartDate = '2012/11/01'
--SET @EndDate = '2012/11/05'


--SELECT
--	DATEDIFF(wk, @StartDate, @EndDate),
--  (DATEDIFF(wk, @StartDate, @EndDate) * 2)
--   +(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday'   THEN 1 ELSE 0 END)
--   +(CASE WHEN DATENAME(dw, @EndDate)   = 'Saturday' THEN 1 ELSE 0 END)

--declare @d1 datetime, @d2 datetime
--select @d1 = '2021-08-01',  @d2 = '2021-08-31'	--GETDATE()

--SELECT 
--	DATEDIFF(dd, @d1, @d2),
--	DATEDIFF(wk, @d1, @d2),
--	DATEDIFF(dd, @d1, @d2) - DATEDIFF(wk, @d1, @d2) * 2 - CASE
--												    WHEN DATEPART(dw, @d1) = 1 THEN 1
--												    ELSE 0
--											    END 
--											  + CASE
--												    WHEN DATEPART(dw, @d2) = 1 THEN 1
--												    ELSE 0
--											    END;

--select
--	(CASE WHEN DATENAME(dw, DATEADD(month, DATEDIFF(month, 0, @tanggal), 0)) = 'Sunday' THEN 1 ELSE 0 END),
--	(CASE WHEN DATENAME(dw, EOMONTH(@tanggal)) = 'Saturday' THEN 1 ELSE 0 END)