USE [db_edits]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- by: Pribadi Pamungkas. 2019-03-27
-- SP untuk memasukan data SPAJ menjadi transaksi di EDITS

CREATE PROCEDURE [dbo].[proc_spaj_transaction]
	@pi_spaj_no VARCHAR(20),
	@po_message VARCHAR(200) OUTPUT
AS
BEGIN

	SET NOCOUNT ON; -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.

	DECLARE @v_user_id VARCHAR(8) = 'SYS'

	IF EXISTS(SELECT spaj_document_id FROM dbo.m_spaj_document WHERE spaj_no=@pi_spaj_no)
		BEGIN

			IF EXISTS(SELECT * FROM t_transaction WHERE policy_id_reference=@pi_spaj_no AND (status_id IN (3,5) OR is_delete=1))	-- selain status Reject atau Cancel dan belum didelete (SELECT * FROM db_edits.dbo.m_transaction_status)
				BEGIN
					SET @po_message = 'SPAJ already exists in transaction'
					RETURN 1
				END
			ELSE
				BEGIN

					-- cari eform yang sesuai
					SELECT
						*
					FROM
						dbo.m_eform AS me INNER JOIN
						dbo.m_transaction_type AS mtt ON me.transaction_type_id = mtt.transaction_type_id INNER JOIN
						dbo.m_transaction_category AS mtc ON mtt.transaction_category_id = mtc.transaction_category_id INNER JOIN
						dbo.m_status AS ms ON me.status_id = ms.status_id
					WHERE
						mtc.transaction_category_code = 'NB'	-- eform kategori new bussiness
						AND ms.status_code = 'STAT02'			-- eform status Active
						AND me.is_delete = 0
						AND mtt.is_delete = 0
						AND mtc.is_delete = 0
					IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Eform not found'
							RETURN 1
						END

					BEGIN TRAN

					INSERT INTO [dbo].[t_transaction]
								([transaction_type_id]
								,[register_number]
								,[transaction_date]
								,[source_app_id]
								,[policy_id_reference]
								,[policy_holder_reference]
								,[product_name_reference]
								,[currency_id_reference]
								,[is_policy_holder]
								,[customer]
								,[address]
								,[mobile_phone]
								,[email_address]
								,[signature_url]
								,[signature_hashcode]
								,[photo_url]
								,[photo_hashcode]
								,[reference_number]
								,[currency_id]
								,[currency_code]
								,[transaction_value]
								,[is_notify_document_completion]
								,[notification_date]
								,[workflow_template_stage_id]
								,[user_id]
								,[branch_id]
								,[status_id]
								,[eform_workflow_id]
								,[source_transaction_id]
								,[created_by]
								,[created_date]
								,[modified_by]
								,[modified_date]
								,[is_delete])
							VALUES
								(72	--<transaction_type_id, int,>		SPAJ OCR (SELECT * FROM db_edits.dbo.m_transaction_type)
								,NULL	--register_number
								,GETDATE()	--transaction_date
								,2	--<source_app_id, int,>			RDS (SELECT * FROM db_edits.dbo.m_source_app)
								,@pi_spaj_no	-- policy_id_reference	-- diisi No SPAJ sbg key di EDITS
								,NULL	--<policy_holder_reference, varchar(100),>
								,NULL	--<product_name_reference, varchar(100),>
								,NULL	--<currency_id_reference, varchar(3),>
								,1	--<is_policy_holder, tinyint,>
								,<customer, varchar(100),>
								,<address, varchar(255),>
								,<mobile_phone, varchar(15),>
								,<email_address, varchar(30),>
								,NULL	--<signature_url, varchar(300),>
								,NULL	--<signature_hashcode, varchar(50),>
								,NULL	--<photo_url, varchar(300),>
								,NULL	--<photo_hashcode, varchar(50),>
								,@pi_spaj_no	-- reference_number	-- diisi no SPAJ sbg key di PL (karena untuk new bussiness di PL key-nya berupa No SPAJ)
								,NULL	--<currency_id, int,>
								,NULL	--<currency_code, varchar(3),>
								,NULL	--<transaction_value, decimal(8,2),>
								,0	--<is_notify_document_completion, tinyint,>
								,NULL	--<notification_date, datetime,>
								,<workflow_template_stage_id, bigint,>
								,@v_user_id	--<user_id, varchar(8),>
								,1	--branch_id	-- HO (SELECT * FROM db_edits.dbo.m_branch)
								,1	--status_id	-- Draft (SELECT * FROM db_edits.dbo.m_transaction_status)
								,<eform_workflow_id, int,>
								,3	--<source_transaction_id, smallint,>		RDS (SELECT * FROM db_edits.dbo.m_source_transaction)
								,@v_user_id
								,GETDATE()
								,@v_user_id
								,GETDATE()
								,0)
    				IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
						BEGIN
							SET @po_message = 'Failed insert t_transaction'
							ROLLBACK TRAN
							RETURN 1
						END

					COMMIT TRAN
	
					SET @po_message = 'Sukses'
					SELECT @po_message  -- dual output stored procedure, variable output dan table output
	
					RETURN 0

				END
		END
	ELSE
		BEGIN
			SET @po_message = 'SPAJ not found'
			RETURN 1
		END

	---- contekan
	
	BEGIN TRAN
	
	DECLARE
	  @v_transaction_document_id BIGINT,
	  @v_user_id VARCHAR(3)
	
	SELECT
		@v_user_id = username
	FROM
		dbo.s_user_session
	WHERE
	  session_id = @pi_session_id
	
  IF EXISTS(
              SELECT
              	*
              FROM
              	dbo.t_transaction_document
              WHERE
                transaction_id = @pi_transaction_id
                AND document_type_id = @pi_document_type_id
           )
    BEGIN  -- jika sudah ada, maka update t_transaction_document
      SELECT 
        @v_transaction_document_id = transaction_document_id
      FROM
        t_transaction_document
      WHERE
        transaction_id = @pi_transaction_id
        AND document_type_id = @pi_document_type_id
      IF (COALESCE(@v_transaction_document_id,'')='')
				BEGIN
					SET @po_message = 'Empty transaction_document_id'
					ROLLBACK TRAN
					RETURN 1
				END

      UPDATE
        t_transaction_document
      SET
        modified_date = GETDATE(),
        modified_by = @v_user_id
      WHERE
        transaction_id = @pi_transaction_id
        AND document_type_id = @pi_document_type_id
      IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
				BEGIN
					SET @po_message = 'Failed update t_transaction_document'
					ROLLBACK TRAN
					RETURN 1
				END
    END
  ELSE
    BEGIN  -- jika belum ada, maka insert t_transaction_document
    	-- start: generate token
      DECLARE	
        @v_retval_token INT,
		    @po_token VARCHAR(10),
		    @v_tipe_token VARCHAR(1) = 'D'

      EXEC	@v_retval_token = [dbo].[proc_generate_token]
		      @pi_tipe = @v_tipe_token,
		      @po_token = @po_token OUTPUT
      IF (@@ERROR <> 0) OR (@v_retval_token <> 0)
	      BEGIN
		      SET @po_message = 'Error generating token.'
		      ROLLBACK TRAN
		      RETURN 1
	      END

      WHILE(
              EXISTS(
                      SELECT
  	                    token
                      FROM
  	                    t_transaction_document
                      WHERE
                        token = @po_token
                    )
           )
        BEGIN
          EXEC	@v_retval_token = [dbo].[proc_generate_token]
		          @pi_tipe = @v_tipe_token,
		          @po_token = @po_token OUTPUT
          IF (@@ERROR <> 0) OR (@v_retval_token <> 0)
	          BEGIN
		          SET @po_message = 'Error generating token.'
		          ROLLBACK TRAN
		          RETURN 1
	          END
        END
    	-- end: generate token
    	
    	INSERT INTO dbo.t_transaction_document
    	        (transaction_id,
    	         document_type_id,
    	         token,
    	         hashcode,
    	         document_index,
    	         document_name,
    	         created_by,
    	         created_date,
    	         modified_by,
    	         modified_date,
    	         is_delete)
    	VALUES
    	        (@pi_transaction_id, -- transaction_id - bigint
    	         @pi_document_type_id, -- document_type_id - int
    	         @po_token, -- token - varchar(30)
    	         NULL, -- hashcode - varchar(30)        -- ini diisi nanti kalau filenya sudah disatukan dalam PDF
    	         NULL, -- document_index - varchar(100) -- URL di DMS (?)
    	         NULL, -- document_name - varchar(100)  -- ini diisi nama file yang akan disimpan di DMS ([Nomor Register]_[Jenis Transaksi]_[Jenis Dokumen])
    	         @v_user_id, -- created_by - varchar(8)
    	         GETDATE(), -- created_date - datetime
    	         @v_user_id, -- modified_by - varchar(8)
    	         GETDATE(), -- modified_date - datetime
    	         '0'  -- is_delete - char(1)
    	         )
    	IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
				BEGIN
					SET @po_message = 'Failed insert t_transaction_document'
					ROLLBACK TRAN
					RETURN 1
				END
    	
    	SELECT @v_transaction_document_id = SCOPE_IDENTITY()
    	IF (COALESCE(@v_transaction_document_id,'')='')
				BEGIN
					SET @po_message = 'Empty identity output from t_transaction_document'
					ROLLBACK TRAN
					RETURN 1
				END
        
    END

  	INSERT INTO dbo.t_transaction_document_file
  	        (transaction_document_id,
  	         document_name,
  	         document_url,
  	         hashcode_ori,
  	         hashcode,
  	         upload_by,
  	         upload_date,
  	         created_by,
  	         created_date,
  	         modified_by,
  	         modified_date,
  	         is_delete)
  	VALUES
  	        (@v_transaction_document_id, -- transaction_document_id - bigint
  	         @pi_document_name, -- document_name - varchar(200)
  	         @pi_document_url, -- document_url - varchar(100)
  	         @pi_hashcode_ori, -- hashcode_ori - varchar(30)
  	         @pi_hashcode, -- hashcode - varchar(30)
  	         @v_user_id, -- upload_by - varchar(8)
  	         COALESCE(@pi_upload_date,GETDATE()), -- upload_date - datetime
  	         @v_user_id, -- created_by - varchar(8)
  	         GETDATE(), -- created_date - datetime
  	         @v_user_id, -- modified_by - varchar(8)
  	         GETDATE(), -- modified_date - datetime
  	         '0'  -- is_delete - char(1)
  	         )
  	IF (@@ROWCOUNT = 0) OR (@@ERROR <> 0)
			BEGIN
				SET @po_message = 'Failed insert t_transaction_document_file'
				ROLLBACK TRAN
				RETURN 1
			END

  
	COMMIT TRAN
	
	SET @po_message = 'Sukses'
	SELECT @po_message  -- dual output stored procedure, variable output dan table output
	
	RETURN 0

END
GO


