SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- contoh dicreate ke stored procedure

CREATE PROCEDURE dbo.laporan_sirdk
	@TopikKode VARCHAR(255)
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 TINYINT,
								jumlah_selesai INT,
								prosentase_selesai TINYINT,
								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

	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 TINYINT = 0,
			@jumlah_selesai INT = 0,
			@prosentase_selesai TINYINT = 0,
			@total_penugasan INT = 0

		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
		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
		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
		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 / NULLIF(@total_penugasan, 0)
		SET @prosentase_selesai = @jumlah_selesai * 100 / NULLIF(@total_penugasan, 0)

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

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

	CLOSE list_cursor  
	DEALLOCATE list_cursor 


	SELECT
		*
	FROM
		@result_table

END
GO
