USE [db_edits]
GO
/****** Object:  UserDefinedFunction [dbo].[func_is_document_complete]    Script Date: 04/16/2018 15:13:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- last modified by Pribadi Pamungkas. 2018-04-16 => bugfix
ALTER FUNCTION [dbo].[func_is_document_complete]
(
	@pi_transaction_id BIGINT
)
RETURNS BIT
AS
BEGIN
	DECLARE 
	  @v_total_row INT,
	  @v_total_valid_row INT,
	  @v_hasil BIT

	SELECT
		@v_total_row = COUNT(med.eform_document_id),
		@v_total_valid_row = SUM(
													CAST(
															CAST(CASE
																WHEN COALESCE(df.jumlah_file,0) < med.minimum_files THEN 0
																ELSE 1
															END AS BIT)
															&
															CAST(CASE
																WHEN COALESCE(df.jumlah_file,0) > med.maximum_files THEN 0
																ELSE 1
															END AS BIT)
															AS INT)
												)
	FROM
		dbo.t_transaction AS t
		INNER JOIN 
		dbo.m_eform AS me ON t.transaction_type_id = me.transaction_type_id
		INNER JOIN 
		dbo.m_eform_document AS med ON me.eform_id = med.eform_id
		INNER JOIN 
		dbo.m_document AS md ON med.document_id = md.document_id
		LEFT JOIN
		(
			SELECT
				ttd.transaction_id,
				ttd.document_type_id,
				COUNT(*) AS jumlah_file
			FROM
				t_transaction_document AS ttd INNER JOIN
				t_transaction_document_file AS ttdf ON ttd.transaction_document_id = ttdf.transaction_document_id
			WHERE
				ttd.is_delete = '0'
				AND ttdf.is_delete = '0'
			GROUP BY
				ttd.transaction_id,
				ttd.document_type_id
		) AS df ON t.transaction_id = df.transaction_id
							 AND md.document_id = df.document_type_id
	WHERE
		(t.is_delete = 0)
		AND (me.is_delete = '0')
		AND (med.is_delete = '0')
		AND (med.status_id = 5)
		AND (md.is_delete = '0')
		AND (
					CASE t.is_policy_holder
						WHEN 1 THEN 'p'
						WHEN 0 THEN 'n'
					END = med.applies_to
					OR med.applies_to = 'a'
				)
		AND t.transaction_id = @pi_transaction_id
  IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			SET @v_hasil = 1	--0
	ELSE
	  BEGIN
      IF @v_total_row = @v_total_valid_row
        SET @v_hasil = 1
      ELSE
        SET @v_hasil = 0
    END

	RETURN @v_hasil

END
