DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN NGÂN HÀNG BẢN VIỆT

ALTER PROCEDURE [dbo].[TR_PO_MASTER_App]

@P_PO_ID VARCHAR(15),

@P_AUTH_STATUS VARCHAR(1),

@P_CHECKER_ID VARCHAR(12),

@P_APPROVE_DT VARCHAR(20)

AS

--Validation is here

DECLARE @ERRORSYS NVARCHAR(15) = '' 

  IF ( NOT EXISTS ( SELECT * FROM TR_PO_MASTER WHERE  PO_ID = @P_PO_ID))

	SET @ERRORSYS = 'PO-00001'

IF @ERRORSYS <> '' 

BEGIN

	SELECT ErrorCode Result, ErrorDesc ErrorDesc FROM SYS_ERROR WHERE ErrorCode = @ERRORSYS

	RETURN '0'

END 

BEGIN TRANSACTION

		DECLARE @ROLE_ID VARCHAR(50)

		SET @ROLE_ID =(SELECT RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)

		SET  @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER WHERE TLNANME =@p_CHECKER_ID)

		IF(@ROLE_ID IS NOT NULL AND @ROLE_ID <>'' AND @ROLE_ID IN ('TPGD','GDDV','PGD','PTGD','TGD','HDQT','TP','PP','TBP','KTT','GDK','KSV','TC','NVTC','TPTC','PPGD'))

		BEGIN

			PRINT @ROLE_ID

		END

		ELSE

		BEGIN

			SET @ROLE_ID =(SELECT TOP 1 RoleName FROM TL_USER_V2 WHERE TLNANME =@p_CHECKER_ID)

			IF(@ROLE_ID IS NULL OR @ROLE_ID ='')

			BEGIN

					SET @ROLE_ID =(SELECT TOP 1 ROLE_NEW FROM TL_SYS_ROLE_MAPPING WHERE ROLE_OLD =(SELECT TOP 1 ROLENAME FROM TL_USER WHERE TLNANME=@p_CHECKER_ID))

			END

		END

		IF(@ROLE_ID NOT IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' REQ_PAY_ID, N'Bạn không nằm trong nhóm quyền được phép phê duyệt hợp đồng' ErrorDesc

			RETURN '-1'

		END

	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID AND AUTH_STATUS ='R'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, N'PO đang bị trả về! Vui lòng đợi nhân viên xử lý và gửi phê duyệt lại' ErrorDesc

			RETURN '-1'

	END

	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID AND AUTH_STATUS ='E'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, N'PO đang trong tình trạng lưu nháp! Vui lòng đợi nhân viên xử lý và gửi phê duyệt lại' ErrorDesc

			RETURN '-1'

	END

	IF(EXISTS(SELECT * FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID AND AUTH_STATUS ='A'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, N'PO này đã được phê duyệt trước đó. Bạn không được phê duyệt lại' ErrorDesc

			RETURN '-1'

		END

		IF((SELECT MAKER_ID FROM TR_PO_MASTER WHERE CONTRACT_ID =@P_PO_ID)=@p_CHECKER_ID AND @ROLE_ID NOT IN ('TPGD','PPGD','PP','TP','GDDV','PGD','KTT','TP','PP','TPTC','TC','GDK'))

		BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, N'Người duyệt PO phải khác người tạo PO' ErrorDesc

			RETURN '-1'

		END

	UPDATE TR_PO_MASTER SET AUTH_STATUS = @P_AUTH_STATUS, CHECKER_ID = @P_CHECKER_ID, APPROVE_DT = CONVERT(DATETIME,@P_APPROVE_DT,103)

	WHERE PO_ID = @P_PO_ID

	IF @@Error <> 0 GOTO ABORT

	DECLARE @L_TRADE_ID VARCHAR(15),

	 @L_QUANTITY INT,

	 @L_PRICE DECIMAL(18,0), @CONTRACT_DT VARCHAR(15)

	DECLARE @L_PLAN_ID VARCHAR(15)

	DECLARE @l_SUMQUANTITY DECIMAL(10,0), @l_SUMAMT DECIMAL(10,0)

	DECLARE @lst_PLANID TABLE(PLAN_ID VARCHAR(15))

	DECLARE TRADE_CURSOR CURSOR FOR

	SELECT A.TRADE_ID,A.PLAN_ID,A.QUANTITY,A.PRICE, A.CONTRACT_DT

	FROM TR_PO_DETAIL A WHERE A.PO_ID = @P_PO_ID

	OPEN TRADE_CURSOR

	PRINT 'DETAIL'

	FETCH NEXT FROM TRADE_CURSOR INTO @L_TRADE_ID,@L_PLAN_ID,@L_QUANTITY,@L_PRICE,@CONTRACT_DT

	WHILE @@FETCH_STATUS = 0

	BEGIN

		IF @L_TRADE_ID IS NOT NULL AND LEN(@L_TRADE_ID)  <>  0

		BEGIN

			PRINT 'UPDATE_TRADE'

			--DAO THEM DIEU KIEN RECORD STATUS CHO TH HUY PO

			SELECT @l_SUMQUANTITY = SUM(ISNULL(QUANTITY,0))

			FROM TR_PO_DETAIL WHERE PLAN_ID = @L_PLAN_ID AND TRADE_ID = @L_TRADE_ID AND RECORD_STATUS='1'

			--UPDATE PL_TRADEDETAIL SET QUANTITY_EXE = (QUANTITY_EXE + CONVERT(INT,@L_QUANTITY)) WHERE TRADE_ID = @L_TRADE_ID

			--UPDATE PL_TRADEDETAIL SET QUANTITY_EXE = @l_SUMQUANTITY	WHERE TRADE_ID = @L_TRADE_ID

		END

		--CAP NHAT SO LUONG DA MUA TRONG HOP DONG THIEUVQ 14092016

		IF @CONTRACT_DT <> '' AND @CONTRACT_DT IS NOT NULL

		BEGIN

			UPDATE TR_CONTRACT_DT SET QUANTITY_USE = (SELECT SUM(A.QUANTITY) FROM TR_PO_DETAIL A WHERE A.CONTRACT_DT = @CONTRACT_DT AND RECORD_STATUS='1')

			WHERE CD_ID = @CONTRACT_DT

		END

		--LUU LAI DANH SACH CAC KE HOACH DUOC GOI TRONG PO

		IF NOT EXISTS(SELECT * FROM @lst_PLANID WHERE PLAN_ID = @L_PLAN_ID)

		INSERT INTO @lst_PLANID VALUES(@L_PLAN_ID)

	-- TRU TIEN CHO KE HOACH

		--PRINT @L_PLAN_ID

		--PRINT @L_QUANTITY

		----UPDATE PL_MASTER SET TOTAL_AMT = (TOTAL_AMT - CONVERT(INT,@L_QUANTITY) * @L_PRICE) WHERE PLAN_ID = @L_PLAN_ID

		--UPDATE PL_MASTER SET TOTAL_AMT = (TOTAL_AMT - CONVERT(INT,@L_QUANTITY) * @L_PRICE) WHERE PLAN_ID = @L_PLAN_ID		

		--IF @@Error <> 0 GOTO ABORT

		FETCH NEXT FROM TRADE_CURSOR INTO @L_TRADE_ID,@L_PLAN_ID,@L_QUANTITY,@L_PRICE, @CONTRACT_DT

	END

	CLOSE TRADE_CURSOR

	DEALLOCATE TRADE_CURSOR

	--TINH TONG TIEN DA THUC HIEN TREN KE HOACH TRONG PO

	SET @L_PLAN_ID = '';

	DECLARE PLCUR CURSOR FOR SELECT * FROM @lst_PLANID

	OPEN PLCUR

	FETCH NEXT FROM PLCUR INTO @L_PLAN_ID

	WHILE @@FETCH_STATUS = 0

	BEGIN

		--UPDATE PL_MASTER SET TOTAL_AMT = (SELECT SUM(ISNULL(QUANTITY,0)*ISNULL(PRICE,0)) 

											--FROM TR_PO_DETAIL WHERE PLAN_ID = @L_PLAN_ID AND RECORD_STATUS='1')

		--WHERE PLAN_ID = @L_PLAN_ID

		FETCH NEXT FROM PLCUR INTO @L_PLAN_ID

	END

	CLOSE PLCUR

	DEALLOCATE PLCUR

	-- TRU SO LUONG TRONG CHI TIET KE HOACH

	-- INSERT VAO LOG

	INSERT INTO dbo.PL_PROCESS

	(

						REQ_ID,

						PROCESS_ID,

						CHECKER_ID,

						APPROVE_DT,

						PROCESS_DESC,NOTES

					)

					VALUES

					(   @p_PO_ID,        -- REQ_ID - varchar(15)

						'APP',        -- PROCESS_ID - varchar(10)

						@P_CHECKER_ID,        -- CHECKER_ID - varchar(15)

						GETDATE(), -- APPROVE_DT - datetime

					   N'Phê duyệt phiếu thành công' ,N'Phê duyệt PO')		

COMMIT TRANSACTION

SELECT '0' as Result, N'PO số: '+(SELECT PO_CODE FROM TR_PO_MASTER WHERE PO_ID =@P_PO_ID)+N' đã được phê duyệt thành công. Bạn có thể thực hiện thanh toán hoặc tạm ứng cho PO' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		PRINT 'ERROR'

		ROLLBACK TRANSACTION

		CLOSE TRADE_CURSOR

		DEALLOCATE TRADE_CURSOR

		SELECT '-1' as Result, '' ErrorDesc

		RETURN '-1'

End