USE [gAMSPro_VietcapitalBank_v2]
GO
/****** Object:  StoredProcedure [dbo].[TR_REQ_DOC_Ins_To_PO]    Script Date: 7/13/2020 2:43:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[TR_REQ_DOC_Ins_To_PO] @p_TR_REQ_ID VARCHAR(15)
AS
BEGIN TRANSACTION;
	DECLARE @l_SUP_ID VARCHAR(15)
	DECLARE @p_PO_CODE VARCHAR(15),
        @p_BRANCH_ID VARCHAR(20),
		@p_SUP_ID VARCHAR(20),
		@p_TOTAL_AMT DECIMAL(18,2),
		@p_MAKER_ID VARCHAR(20),
		@p_TR_REQ_CODE VARCHAR(20),
		@p_SUP_NAME NVARCHAR(200),
		@p_SUP_ADDR NVARCHAR(200),
		@p_BRANCH_RE VARCHAR(20),
        @p_PO_NAME NVARCHAR(100);
--- LAY DANH SACH NHA CUNG CAP CUA HANG HOA TRONG PYCMS - LUCTV 25052020
	DECLARE @TABLE_NCC TABLE (SUP_ID VARCHAR(15))
	INSERT INTO @TABLE_NCC SELECT SUP_ID FROM TR_REQUEST_DOC_DT WHERE REQ_DOC_ID =@p_TR_REQ_ID GROUP BY SUP_ID
	WHILE((SELECT COUNT(*) FROM @TABLE_NCC)>0)
	BEGIN
	SET @l_SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
	--insert master		
	DECLARE @l_PO_ID VARCHAR(15);
	EXEC SYS_CodeMasters_Gen 'TR_PO_MASTER', @l_PO_ID OUT;
	IF @l_PO_ID = '' OR @l_PO_ID IS NULL
	GOTO ABORT;
	SELECT @p_BRANCH_ID=BRANCH_DVMS,@p_BRANCH_RE=BRANCH_CREATE,@p_TR_REQ_CODE=REQ_CODE,@p_PO_NAME=REQ_REASON,@p_MAKER_ID=USER_DVMS FROM dbo.TR_REQUEST_DOC WHERE REQ_ID=@p_TR_REQ_ID

	--SELECT TOP 1 @p_SUP_ID = SUP_ID FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID
	SET @p_SUP_ID =@l_SUP_ID
	SET @p_TOTAL_AMT= (SELECT SUM(TOTAL_AMT) FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)
	--TU PHAT SINH SO PO
	DECLARE @ldate INT = (SELECT YEAR(GETDATE()) );
	EXEC [dbo].[TR_PO_MASTER_NO_Gen] @p_BRANCH_ID, @ldate, @p_PO_CODE OUT;
	IF @p_PO_CODE = ''
	   OR @p_PO_CODE IS NULL
		GOTO ABORT;

	INSERT INTO TR_PO_MASTER
	(
		[PO_TYPE],
		[PO_ID],
		[PO_CODE],
		[PO_NAME],
		[CONTRACT_ID],
		[SUP_ID],
		[SUP_NAME],
		[SUP_ADDR],
		[INPUT_DT],
		[PAYMENT_DT],
		[TOTAL_AMT],
		[REQ_DOC_ID],
		[DELIVERY_DT],
		[PAYAPP_DT],
		[NOTES],
		[RECORD_STATUS],
		[MAKER_ID],
		[CREATE_DT],
		[AUTH_STATUS],
		[CHECKER_ID],
		[APPROVE_DT],
		BRANCH_ID
	)
	VALUES
	(1, @l_PO_ID, @p_PO_CODE, @p_PO_NAME, '', @p_SUP_ID, @p_SUP_NAME, @p_SUP_ADDR, GETDATE(),
	 NULL, @p_TOTAL_AMT, @p_TR_REQ_ID, NULL,
	 NULL, '', '1', @p_MAKER_ID, GETDATE(),
	 'E', NULL, NULL, @p_BRANCH_ID);
	IF @@Error <> 0
		GOTO ABORT;
	PRINT 'INSERT MASTER SUCCESS';
	INSERT INTO dbo.TR_PO_MASTER_TEMP
	(
		PO_ID,
		TR_REQ_ID,
		TR_REQ_CODE
	)
	VALUES
	(   @l_PO_ID,      -- PO_ID - varchar(15)
		@p_TR_REQ_ID,  -- TR_REQ_ID - varchar(20)
		@p_TR_REQ_CODE -- TR_REQ_CODE - varchar(20)
	 );
	 DECLARE @HANGHOA_ID VARCHAR(20),@QUANTITY DECIMAL(18,0),@PRICE DECIMAL(18,2),@TOTAL_AMT DECIMAL(18,2),@HH_TYPE VARCHAR(20),@HH_NAME NVARCHAR(100)
	 DECLARE lstData CURSOR FOR
	 SELECT HANGHOA_ID,QUANTITY,PRICE,TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID
		
	OPEN lstData
	FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
	WHILE @@FETCH_STATUS = 0
	BEGIN
	SELECT @HH_NAME= HH_NAME,@HH_TYPE=HH_TYPE_ID FROM dbo.CM_HANGHOA WHERE HH_ID=@HANGHOA_ID
	DECLARE @l_PD_ID VARCHAR(15)
			EXEC SYS_CodeMasters_Gen 'TR_PO_DETAIL', @l_PD_ID out
			IF @l_PD_ID='' OR @l_PD_ID IS NULL GOTO ABORT		

	 INSERT INTO dbo.TR_PO_DETAIL
	 (
		 PD_ID,
		 PO_ID,
		 PLAN_ID,
		 TRADE_ID,
		 GOODS_ID,
		 DESCRIPTION,
		 UNIT_ID,
		 QUANTITY,
		 PRICE,
		 TOTAL_AMT,
		 IS_DELIVERY,
		 DELIVERY_DT,
		 PAYMENT_STATUS,
		 AMOUNT_PAID,
		 PAID_DT,
		 INVOICENO,
		 NOTES,
		 RECEIVE_BRANCH,
		 RECEIVE_ADDR,
		 RECEIVE_PERSON,
		 RECEIVE_TEL,
		 RECORD_STATUS,
		 MAKER_ID,
		 CREATE_DT,
		 AUTH_STATUS,
		 CHECKER_ID,
		 APPROVE_DT,
		 EXP_DELIVERY_DT,
		 GOODS_NAME,
		 INVOICE_DT,
		 GOODSTYPE_REAL,
		 VAT,
		 PRICE_VAT,
		 CONTRACT_DT
	 )
	 VALUES
	 (   @l_PD_ID,        -- PD_ID - varchar(15)
		 @l_PO_ID,        -- PO_ID - varchar(15)
		 '',        -- PLAN_ID - varchar(15)
		 '',        -- TRADE_ID - varchar(15)
		 @HANGHOA_ID,        -- GOODS_ID - varchar(15)
		 N'',       -- DESCRIPTION - nvarchar(500)
		 '',        -- UNIT_ID - varchar(15)
		 @QUANTITY,      -- QUANTITY - decimal(18, 0)
		 ROUND(@PRICE/1.1,0),      -- PRICE - decimal(18, 0)
		 @TOTAL_AMT,      -- TOTAL_AMT - decimal(18, 0)
		 '',        -- IS_DELIVERY - varchar(1)
		 NULL, -- DELIVERY_DT - datetime
		 'CTT',        -- PAYMENT_STATUS - varchar(4)
		 NULL,      -- AMOUNT_PAID - decimal(18, 0)
		 NULL, -- PAID_DT - datetime
		 '',        -- INVOICENO - varchar(1000)
		 N'',       -- NOTES - nvarchar(1000)
		 @p_BRANCH_RE,        -- RECEIVE_BRANCH - varchar(15)
		 N'',       -- RECEIVE_ADDR - nvarchar(1000)
		 N'',       -- RECEIVE_PERSON - nvarchar(500)
		 '',        -- RECEIVE_TEL - varchar(100)
		 '',        -- RECORD_STATUS - varchar(1)
		 @p_MAKER_ID,        -- MAKER_ID - varchar(15)
		 GETDATE(), -- CREATE_DT - datetime
		 '',        -- AUTH_STATUS - varchar(50)
		 '',        -- CHECKER_ID - varchar(15)
		 NULL, -- APPROVE_DT - datetime
		 NULL, -- EXP_DELIVERY_DT - datetime
		 @HH_NAME,       -- GOODS_NAME - nvarchar(500)
		 NULL, -- INVOICE_DT - datetime
		 @HH_TYPE,        -- GOODSTYPE_REAL - varchar(15)
		 10,      -- VAT - decimal(18, 2)
		--@TOTAL_AMT -   (ROUND(@PRICE/1.1,0) *@QUANTITY),      -- PRICE_VAT - decimal(18, 0)
		 @PRICE -ROUND(@PRICE/1.1,0),
		 ''         -- CONTRACT_DT - varchar(15)
		 )
 
	   FETCH NEXT FROM lstData INTO @HANGHOA_ID,@QUANTITY,@PRICE,@TOTAL_AMT
	END
	 CLOSE lstData;
    DEALLOCATE lstData;

	SET @TOTAL_AMT=(SELECT SUM(TOTAL_AMT) AS TOTAL_AMT FROM dbo.TR_REQUEST_DOC_DT WHERE REQ_DOC_ID=@p_TR_REQ_ID AND SUP_ID =@p_SUP_ID)

	DECLARE @l_PAY_ID VARCHAR(15)
	EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
	IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
	INSERT INTO dbo.TR_PO_PAYMENT
	(
	    PAY_ID,
	    PO_ID,
	    PAY_PHASE,
	    EXP_DT,
	    [PERCENT],
	    AMOUNT,
	    NOTES,
	    RECORD_STATUS,
	    MAKER_ID,
	    CREATE_DT,
	    AUTH_STATUS,
	    CHECKER_ID,
	    APPROVE_DT
	)
	VALUES
	(   @l_PAY_ID,        -- PAY_ID - varchar(15)
	    @l_PO_ID,        -- PO_ID - varchar(15)
	    '1',        -- PAY_PHASE - varchar(20)
	    GETDATE(), -- EXP_DT - datetime
	    50,      -- PERCENT - decimal(18, 0)
	    @TOTAL_AMT / 2 ,      -- AMOUNT - decimal(18, 0)
	    N'',       -- NOTES - nvarchar(1000)
	    '1',        -- RECORD_STATUS - varchar(1)
	    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
	    GETDATE(), -- CREATE_DT - datetime
	    'U',        -- AUTH_STATUS - varchar(50)
	    '',        -- CHECKER_ID - varchar(15)
	    NULL  -- APPROVE_DT - datetime
	    )
		EXEC SYS_CodeMasters_Gen 'TR_PO_PAYMENT', @l_PAY_ID out
	IF @l_PAY_ID='' OR @l_PAY_ID IS NULL GOTO ABORT
	INSERT INTO dbo.TR_PO_PAYMENT
	(
	    PAY_ID,
	    PO_ID,
	    PAY_PHASE,
	    EXP_DT,
	    [PERCENT],
	    AMOUNT,
	    NOTES,
	    RECORD_STATUS,
	    MAKER_ID,
	    CREATE_DT,
	    AUTH_STATUS,
	    CHECKER_ID,
	    APPROVE_DT
	)
	VALUES
	(   @l_PAY_ID,        -- PAY_ID - varchar(15)
	    @l_PO_ID,        -- PO_ID - varchar(15)
	    '2',        -- PAY_PHASE - varchar(20)
	    GETDATE(), -- EXP_DT - datetime
	    50,      -- PERCENT - decimal(18, 0)
	    @TOTAL_AMT / 2 ,      -- AMOUNT - decimal(18, 0)
	    N'',       -- NOTES - nvarchar(1000)
	    '1',        -- RECORD_STATUS - varchar(1)
	    @p_MAKER_ID,        -- MAKER_ID - varchar(15)
	    GETDATE(), -- CREATE_DT - datetime
	    'E',        -- AUTH_STATUS - varchar(50)
	    '',        -- CHECKER_ID - varchar(15)
	    NULL  -- APPROVE_DT - datetime
	    )

	IF(@TOTAL_AMT <=5000000)
	BEGIN
		UPDATE dbo.TR_PO_MASTER SET AUTH_STATUS='A' WHERE PO_ID=@l_PO_ID
	END
	--XOA DI NHA CUNG CAP
	DELETE FROM @TABLE_NCC WHERE SUP_ID =(SELECT TOP 1 SUP_ID FROM @TABLE_NCC)
	--- KET THUC VONG LAP KIEM TRA DANH SACH NHA CUNG CAP PYCMS
	END
	COMMIT TRANSACTION;
	RETURN 1;
	ABORT:
	BEGIN
		ROLLBACK TRANSACTION;
		RETURN 1;
	END;
	ABORT1:
	BEGIN
		CLOSE lstData;
		DEALLOCATE lstData;
		ROLLBACK TRANSACTION;
		RETURN 1;
	END;