USE [SISIMPU]
GO

/****** Object:  View [Transaction].[View_Synchronization]    Script Date: 21/09/2021 01:16:04 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER 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.OrganizationMappingDhuk,
	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.SynchronizationKementrianRequestNDNomor,
	S.SynchronizationKementrianRequestNDDate,
	S.SynchronizationKementrianRequestNDDocumentId,
	S.SynchronizationKementrianRequestDate,
	S.SynchronizationResponseNDNomor,
	S.SynchronizationResponseNDDate,
	S.SynchronizationResponseNDDocumentId,
	S.SynchronizationResponseDate,
	S.SynchronizationCreateBy,
	S.SynchronizationCreateDate,
	S.SynchronizationUpdateBy,
	S.SynchronizationUpdateDate,
	S.SynchronizationStatus,

	--R.RdkDate,
	CASE
		----WHEN S.SynchronizationId	IS NULL AND D.RegulationStep=23 THEN	-- jika belum ada pengharmonisasian
		--WHEN D.RegulationStep=23 THEN	
		--								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.SynchronizationRequestDate IS NULL AND D.RegulationStep=23 THEN	-- jika belum ada pengharmonisasian
										CASE
											WHEN D.ConditionCode = '01' THEN	--Normal
												CASE
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) < 0 THEN '<span class="text-danger">Overdue</span>'
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) = 0 THEN '<span class="text-warning">Last day</span>'
													ELSE '<span class="text-success">' + CAST(3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) AS varchar(8)) + ' day(s) remaining</span>'
												END
											WHEN D.ConditionCode = '02' THEN	--Mendesak
												CASE
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) < 0 THEN '<span class="text-danger">Overdue</span>'
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) = 0 THEN '<span class="text-warning">Last day</span>'
													ELSE '<span class="text-success">' + CAST(2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, GETDATE()) AS varchar(8)) + ' day(s) remaining</span>'
												END
										END
		-- WHEN harmonization request
		WHEN S.SynchronizationRequestDate IS NOT NULL THEN
										CASE
											WHEN D.ConditionCode = '01' THEN	--Normal
												CASE
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, S.SynchronizationRequestDate) < 0 THEN '<span class="text-danger">Overdue</span>'
													WHEN 3 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, S.SynchronizationRequestDate) >= 0 THEN '<span class="text-info">On time</span>'
													ELSE NULL
												END
											WHEN D.ConditionCode = '02' THEN	--Mendesak
												CASE
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, S.SynchronizationRequestDate) < 0 THEN '<span class="text-danger">Overdue</span>'
													WHEN 2 - [Master].[FN_GetWorkingDaysCount] (R.RdkDate, S.SynchronizationRequestDate) = 0 THEN '<span class="text-info">On time</span>'
													ELSE NULL
												END
										END
		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 'Lanjut Ke Proses Pengharmonisasian DHUK'
		--WHEN R.RdkType = 2
		--THEN 'RDK Dengan Perubahan Rancangan'
		WHEN R.RdkType = 3
		THEN 'Perubahan Rancangan'
		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,
	F1.DocumentFileName AS NDPermintaanPengharmonisasianDocumentFileName,
	F1.DocumentPath AS NDPermintaanPengharmonisasianDocumentPath,
	F2.DocumentFileName AS NDPenyampaianPengharmonisasianDocumentFileName,
	F2.DocumentPath AS NDPenyampaianPengharmonisasianDocumentPath
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
	LEFT OUTER JOIN
	[Transaction].Document AS F1 ON
		F1.DocumentDataId = D.RegulationId
		AND F1.DocumentType = 1027	-- Permintaan
		AND F1.DocumentStatus = 1
	LEFT OUTER JOIN
	[Transaction].Document AS F2 ON
		F2.DocumentDataId = D.RegulationId
		AND F2.DocumentType = 1028	-- Penyampaian Hasil
		AND F2.DocumentStatus = 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.RegulationStep IN (5, 6, 7, 8, 17, 18, 19, 23)
			OR S.SynchronizationId IS NOT NULL
		)
	AND (D.RegulationTypeId = 1)
	AND (D.RegulationRegulationTypeId = 1)
	AND (R.RdkDate IS NOT NULL OR D.RegulationConditionId <> 1)

GO


