USE [OJK SIRDK]
GO
/****** Object:  StoredProcedure [Transaction].[Proc_GetStatusPenugasanRDK_Bidang]    Script Date: 28/07/2021 15:22:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




-- contoh dicreate ke stored procedure

ALTER PROCEDURE [Transaction].[Proc_GetStatusPenugasanRDK_Bidang]
	--@TopikKode VARCHAR(255)
	@DateStart DATE = '2019-01-01',
	@DateEnd DATE  = '2021-01-01'
AS
BEGIN
	
	SET NOCOUNT ON;-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE @result_table TABLE ( 
								sektor VARCHAR(255),
								depkom VARCHAR(255),
								departemen VARCHAR(255),
								menunggu_respon INT,
								dalam_proses INT,
								jumlah_belum_selesai INT,
								prosentase_belum_selesai NUMERIC(5,2),
								jumlah_selesai INT,
								prosentase_selesai  NUMERIC(5,2),
								total_penugasan INT
							)

	DECLARE 
		@AdkOrganizationName VARCHAR(255),
		@DeputiOrganizationInitial VARCHAR(255),
		@SatkerOrganizationInitial VARCHAR(255),
		@OrganizationCode VARCHAR(255)

	DECLARE list_cursor CURSOR FAST_FORWARD FOR  
	SELECT 
		AdkOrganizationName,
		DeputiOrganizationInitial,
		SatkerOrganizationInitial,
		OrganizationCode
	FROM 
		[Master].OrganizationHirarki
	WHERE
		OrganizationStatus = 1
		AND OrganizationLevel <= 3
		AND OrganizationIsSatuanKerja = 1
	ORDER BY
		LEFT(KodeAdk, 3),
		DeputiOrganizationInitial,
		SatkerOrganizationInitial

	DECLARE
		@last_AdkOrganizationName VARCHAR(255) = '',
		@last_DeputiOrganizationInitial VARCHAR(255) = ''

	OPEN list_cursor  
	FETCH NEXT FROM list_cursor INTO @AdkOrganizationName, @DeputiOrganizationInitial, @SatkerOrganizationInitial, @OrganizationCode

	WHILE @@FETCH_STATUS = 0  
	BEGIN

		DECLARE
			@menunggu_respon INT = 0,
			@dalam_proses INT = 0,
			@jumlah_belum_selesai INT = 0,
			@prosentase_belum_selesai NUMERIC(5,2) = 0,
			@jumlah_selesai INT = 0,
			@prosentase_selesai NUMERIC(5,2) = 0,
			@total_penugasan INT = 0

		-- saran untuk meningkatkan performa (kecepatan):
		-- jangan query dari view, tapi langsung ke tabelnya dan hanya untuk tabel-tabel yang dibutuhkan saja (join disini saja)
		-- sebaiknya gunakan panjang data sesuai yang diperlukan saja, misal jangan menggunakan VARCHAR(255) untuk kode satker/depkom dsb, secukupnya saja misal VARCHAR(6), dan juga untuk tipe data lainnya, lalu beri index jika diperlukan
		SELECT 
			@menunggu_respon = COALESCE(COUNT(AssignmentDetailStatus), 0)
		FROM 
			[Transaction].View_AssignmentDetails
		WHERE
			1 = 1
			AND OrganizationStatus = 1
			AND OrganizationLevel = 3
			AND AssignmentDetailStatus = 1		
			--AND TopikKode = @TopikKode
			AND OrganizationCode = @OrganizationCode
			AND TopikTanggalRdkBS BETWEEN @DateStart AND @DateEnd
		GROUP BY 
			OrganizationCode

		SELECT 
			@dalam_proses = COALESCE(COUNT(AssignmentDetailStatus), 0)
		FROM 
			[Transaction].View_AssignmentDetails
		WHERE
			1 = 1
			AND OrganizationStatus = 1
			AND OrganizationLevel = 3
			AND AssignmentDetailStatus = 2
			--AND TopikKode = @TopikKode
			AND OrganizationCode = @OrganizationCode
			AND TopikTanggalRdkBS BETWEEN @DateStart AND @DateEnd
		GROUP BY 
			OrganizationCode

		SELECT 
			@jumlah_selesai = COALESCE(COUNT(AssignmentDetailStatus), 0)
		FROM 
			[Transaction].View_AssignmentDetails
		WHERE
			1 = 1
			AND OrganizationStatus = 1
			AND OrganizationLevel = 3
			AND AssignmentDetailStatus = 3
			--AND TopikKode = @TopikKode
			AND OrganizationCode = @OrganizationCode
			AND TopikTanggalRdkBS BETWEEN @DateStart AND @DateEnd
		GROUP BY 
			OrganizationCode

		SET @jumlah_belum_selesai = @menunggu_respon + @dalam_proses
		SET @total_penugasan = @jumlah_belum_selesai + @jumlah_selesai
		SET @prosentase_belum_selesai = @jumlah_belum_selesai * 100.00 / NULLIF(@total_penugasan, 0)
		SET @prosentase_selesai = @jumlah_selesai * 100.00 / NULLIF(@total_penugasan, 0)

		DECLARE
			@text_AdkOrganizationName VARCHAR(255) = '',
			@text_DeputiOrganizationInitial VARCHAR(255) = ''

		IF @last_AdkOrganizationName <> @AdkOrganizationName
			SET @text_AdkOrganizationName = @AdkOrganizationName

		IF @last_DeputiOrganizationInitial <> @DeputiOrganizationInitial
			SET @text_DeputiOrganizationInitial = @DeputiOrganizationInitial

		INSERT INTO @result_table ( 
								sektor,
								depkom,
								departemen,
								menunggu_respon,
								dalam_proses,
								jumlah_belum_selesai,
								prosentase_belum_selesai,
								jumlah_selesai,
								prosentase_selesai,
								total_penugasan
							)
			VALUES
			(
				@text_AdkOrganizationName, 
				@text_DeputiOrganizationInitial, 
				@SatkerOrganizationInitial,
				@menunggu_respon,
				@dalam_proses,
				@jumlah_belum_selesai,
				COALESCE(@prosentase_belum_selesai, 0),
				@jumlah_selesai,
				COALESCE(@prosentase_selesai, 0),
				@total_penugasan
			)

		SET @last_AdkOrganizationName = @AdkOrganizationName
		SET @last_DeputiOrganizationInitial = @DeputiOrganizationInitial

		FETCH NEXT FROM list_cursor INTO @AdkOrganizationName, @DeputiOrganizationInitial, @SatkerOrganizationInitial, @OrganizationCode
	END  

	CLOSE list_cursor  
	DEALLOCATE list_cursor 


	--SELECT
	--	*
	--FROM
	--	@result_table

	SELECT
		sektor,
		depkom,
		departemen,
		CAST(menunggu_respon AS VARCHAR(8)) AS menunggu_respon,
		CAST(dalam_proses AS VARCHAR(8)) AS dalam_proses,
		CAST(jumlah_belum_selesai AS VARCHAR(8)) AS jumlah_belum_selesai,
		STR(prosentase_belum_selesai,6,2) + '%' AS prosentase_belum_selesai,
		CAST(jumlah_selesai AS VARCHAR(8)) AS jumlah_selesai,
		STR(prosentase_selesai,6,2) + '%' AS prosentase_selesai,
		CAST(total_penugasan AS VARCHAR(8)) AS total_penugasan
	FROM
		@result_table
	UNION ALL
	--SELECT  
	--	sektor,
	--	depkom,
	--	departemen,
	--	'<span style="font-size:13px;font-weight: bold;">'+LTRIM(STR(menunggu_respon,10))+'</span>' AS menunggu_respon,
	--	dalam_proses,
	--	jumlah_belum_selesai,
	--	prosentase_belum_selesai,
	--	jumlah_selesai,
	--	prosentase_selesai,
	--	total_penugasan
	--	FROM (	
	--SELECT
	--	'<span style="font-size:13px;font-weight: bold;" >JUMLAH PENUGASAN UNTUK SATKER SAAT INI</span>' AS sektor,
	--	'' AS depkom,
	--	'' AS departemen,
	--	SUM(menunggu_respon) AS menunggu_respon,
	--	SUM(dalam_proses) AS dalam_proses,
	--	SUM(jumlah_belum_selesai) AS jumlah_belum_selesai,
	--	SUM(jumlah_belum_selesai) * 100 / NULLIF(SUM(total_penugasan),0) AS prosentase_belum_selesai,
	--	SUM(jumlah_selesai) AS jumlah_selesai,
	--	SUM(jumlah_selesai) * 100 / NULLIF(SUM(total_penugasan),0) AS prosentase_selesai,
	--	SUM(total_penugasan) AS total_penugasan
	--FROM
	--	@result_table) as t
	
	SELECT
		'<span style="font-size:13px;font-weight: bold;" >JUMLAH PENUGASAN UNTUK SATKER SAAT INI</span>' AS sektor,
		'' AS depkom,
		'' AS departemen,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(SUM(menunggu_respon) AS VARCHAR(8)) + '</span>' AS menunggu_respon,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(SUM(dalam_proses) AS VARCHAR(8)) + '</span>' AS dalam_proses,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(SUM(jumlah_belum_selesai) AS VARCHAR(8)) + '</span>' AS jumlah_belum_selesai,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(STR(SUM(jumlah_belum_selesai) * 100.00 / NULLIF(SUM(total_penugasan),0),6,2) AS VARCHAR(80)) + '%</span>' AS prosentase_belum_selesai,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(SUM(jumlah_selesai) AS VARCHAR(8)) + '</span>' AS jumlah_selesai,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(STR(SUM(jumlah_selesai) * 100.00 / NULLIF(SUM(total_penugasan),0),6,2) AS VARCHAR(80)) + '%</span>' AS prosentase_selesai,
		'<span style="font-size:13px;font-weight: bold;">' + CAST(SUM(total_penugasan) AS VARCHAR(8)) + '</span>' AS total_penugasan
	FROM
		@result_table

END
