DỰ ÁN PHẦN MỀM QUẢN LÝ TÀI SẢN - NH BẢN VIỆT - GD3

ALTER   PROCEDURE [dbo].[TR_REQ_DOC_XETGIA_DUOI_100M_Ins]

	--@p_REQ_CODE	varchar(15) NULL,

	@p_FR_DATE varchar(30)  NULL,

	@p_CONCLUSION nvarchar(MAX) NULL,

	@p_NOTES nvarchar(MAX) NULL,

	@p_RECORD_ID varchar(15) NULL,

	@p_REQ_DOC_ID varchar(15) NULL,

	--------BAODNQ 5/3/2022 : Truyền thêm tham số---------

	@p_RECORD_STATUS VARCHAR(1) = NULL,

	@p_AUTH_STATUS VARCHAR(1) = NULL,

	@p_MAKER_ID VARCHAR(15) = NULL,

	@p_CREATE_DT VARCHAR(20) = NULL,

	@p_CHECKER_ID VARCHAR(15) = NULL,

	@p_APPROVE_DT VARCHAR(20) = NULL,

	@p_ABOUT NVARCHAR(4000) = NULL,

	@p_WIN_SUPPLIER NVARCHAR(1000) = NULL,

	@p_CURRENCY VARCHAR(15) = NULL,

	@p_TOTAL_SUPPLIERS INT = NULL,

	---------------

	@p_ADDNEW_DT xml

AS

BEGIN TRY		

	DECLARE @tr_ADDNEW_ID VARCHAR(15)

	EXEC SYS_CodeMasters_Gen 'TR_REQ_DOC_XETGIA_DUOI_100M', @tr_ADDNEW_ID out

	------------------BAODNQ 5/3/2022 Thêm tự động sinh mã REQ_CODE--------

	DECLARE @p_REQ_CODE VARCHAR(200)

	EXEC TR_REPORT_PRICE_CODE_GenKey 'TR_REPORT_PRICE', '', '', @p_REQ_CODE OUT

	-----------BAODNQ 5/3/2022 Thêm các cột vào bảng TR_REQ_DOC_XETGIA_DUOI_100M--------

	INSERT INTO TR_REQ_DOC_XETGIA_DUOI_100M ([RECORD_ID],[REQ_DOC_ID],[REQ_CODE],[FR_DATE],[CONCLUSION],

		[NOTES], [RECORD_STATUS],[AUTH_STATUS],[MAKER_ID],[CREATE_DT],[CHECKER_ID],[APPROVE_DT],

		[ABOUT], [WIN_SUPPLIER], [CURRENCY], [TOTAL_SUPPLIERS])

     VALUES (@tr_ADDNEW_ID,@p_REQ_DOC_ID,@p_REQ_CODE,CONVERT(DATETIME,@p_FR_DATE,103),@p_CONCLUSION,

		@p_NOTES, @p_RECORD_STATUS, @p_AUTH_STATUS, @p_MAKER_ID,CONVERT(DATETIME, @p_CREATE_DT, 103),

		@p_CHECKER_ID,

		CONVERT(DATETIME, @p_APPROVE_DT, 103), @p_ABOUT, @p_WIN_SUPPLIER, @p_CURRENCY, 

		@p_TOTAL_SUPPLIERS)

	DECLARE @hdoc int

	Exec sp_xml_preparedocument @hdoc Output, @p_ADDNEW_DT	

	DECLARE AddnewDT CURSOR FOR

	SELECT *

	FROM OPENXML(@hDoc,'/Root/ADDNEWDT',2)

	WITH 

	(

		RECORD_MASTER_ID	varchar(15),

		GOOD_NAME	nvarchar(250),

		HH_ID VARCHAR(15),

		TECH_SPECIFICATION	nvarchar(MAX),

		UNIT	nvarchar(100),

		QUANTITY	int,

		SUPPLIER_1	nvarchar(100),

		UNIT_PRICE_1	numeric(18, 2),

		TOTAL_AMT_1	numeric(18, 2),

		VAT_AMT_1 NUMERIC(18,2),

		TOTAL_AMT_AFTER_VAT_1 NUMERIC(18,2),

		SUPPLIER_2	nvarchar(100),

		UNIT_PRICE_2	numeric(18, 2),

		TOTAL_AMT_2	numeric(18, 2),

		VAT_AMT_2 NUMERIC(18,2),

		TOTAL_AMT_AFTER_VAT_2 NUMERIC(18,2),

		SUPPLIER_3	nvarchar(100),

		UNIT_PRICE_3	numeric(18, 2),

		TOTAL_AMT_3	numeric(18, 2),

		VAT_AMT_3 NUMERIC(18,2),

		TOTAL_AMT_AFTER_VAT_3 NUMERIC(18,2),

		SUPPLIER_4	nvarchar(100),

		UNIT_PRICE_4	numeric(18, 2),

		TOTAL_AMT_4	numeric(18, 2),

		VAT_AMT_4 NUMERIC(18,2),

		TOTAL_AMT_AFTER_VAT_4 NUMERIC(18,2),

		SUPPLIER_5	nvarchar(100),

		UNIT_PRICE_5	numeric(18, 2),

		TOTAL_AMT_5	numeric(18, 2),

		VAT_AMT_5 NUMERIC(18,2),

		TOTAL_AMT_AFTER_VAT_5 NUMERIC(18,2),

		NOTES	nvarchar(MAX),

		RECORD_DT_ID	varchar(15),

		TR_REQ_DOC_DT_ID VARCHAR(15),

		WIN_SUPPLIER_PRICE DECIMAL(18,2)

	)

	OPEN AddnewDT

	BEGIN TRANSACTION

	------------------------------------------------------------

	DECLARE @RECORD_MASTER_ID	varchar(15),

		@GOOD_NAME	nvarchar(250),

		@HH_ID VARCHAR(15),

		@TECH_SPECIFICATION	nvarchar(MAX),

		@UNIT	nvarchar(100),

		@QUANTITY	int,

		@SUPPLIER_1	nvarchar(100),

		@UNIT_PRICE_1	numeric(18, 2),

		@TOTAL_AMT_1	numeric(18, 2),

		@VAT_AMT_1 NUMERIC(18,2),

		@TOTAL_AMT_AFTER_VAT_1 NUMERIC(18,2),

		@SUPPLIER_2	nvarchar(100),

		@UNIT_PRICE_2	numeric(18, 2),

		@TOTAL_AMT_2	numeric(18, 2),

		@VAT_AMT_2 NUMERIC(18,2),

		@TOTAL_AMT_AFTER_VAT_2 NUMERIC(18,2),

		@SUPPLIER_3	nvarchar(100),

		@UNIT_PRICE_3	numeric(18, 2),

		@TOTAL_AMT_3	numeric(18, 2),

		@VAT_AMT_3 NUMERIC(18,2),

		@TOTAL_AMT_AFTER_VAT_3 NUMERIC(18,2),

		@SUPPLIER_4	NVARCHAR(100),

		@UNIT_PRICE_4	NUMERIC(18, 2),

		@TOTAL_AMT_4	NUMERIC(18, 2),

		@VAT_AMT_4 NUMERIC(18,2),

		@TOTAL_AMT_AFTER_VAT_4 NUMERIC(18,2),

		@SUPPLIER_5	NVARCHAR(100),

		@UNIT_PRICE_5	NUMERIC(18, 2),

		@TOTAL_AMT_5	NUMERIC(18, 2),

		@VAT_AMT_5 NUMERIC(18,2),

		@TOTAL_AMT_AFTER_VAT_5 NUMERIC(18,2),

		@NOTES	nvarchar(MAX),

		@RECORD_DT_ID	varchar(15),

		@TR_REQ_DOC_DT_ID VARCHAR(15),

		@WIN_SUPPLIER_PRICE DECIMAL(18,2)

	FETCH NEXT FROM AddnewDT INTO 

		@RECORD_MASTER_ID, 

		@GOOD_NAME,

		@HH_ID,

		@TECH_SPECIFICATION	, 

		@UNIT, 

		@QUANTITY, 

		@SUPPLIER_1, 

		@UNIT_PRICE_1,

		@TOTAL_AMT_1,

		@VAT_AMT_1,

		@TOTAL_AMT_AFTER_VAT_1,

		@SUPPLIER_2, 

		@UNIT_PRICE_2,

		@TOTAL_AMT_2,

		@VAT_AMT_2,

		@TOTAL_AMT_AFTER_VAT_2,

		@SUPPLIER_3, 

		@UNIT_PRICE_3,

		@TOTAL_AMT_3,

		@VAT_AMT_3,

		@TOTAL_AMT_AFTER_VAT_3,

		@SUPPLIER_4,

		@UNIT_PRICE_4,

		@TOTAL_AMT_4,

		@VAT_AMT_4,

		@TOTAL_AMT_AFTER_VAT_4,

		@SUPPLIER_5,

		@UNIT_PRICE_5,

		@TOTAL_AMT_5,

		@VAT_AMT_5,

		@TOTAL_AMT_AFTER_VAT_5,

		@NOTES, 

		@RECORD_DT_ID,

		@TR_REQ_DOC_DT_ID,

		@WIN_SUPPLIER_PRICE

	WHILE @@FETCH_STATUS = 0	

	BEGIN			

		DECLARE @tr_ADDNEWDT_ID VARCHAR(15)

		EXEC SYS_CodeMasters_Gen 'TR_REQ_DOC_XETGIA_DUOI_100M_DT', @tr_ADDNEWDT_ID out

		IF @tr_ADDNEWDT_ID='' OR @tr_ADDNEWDT_ID IS NULL GOTO ABORT		

		----------Tính giá trị thành tiền trước thuế----------

		SET @TOTAL_AMT_1 = @UNIT_PRICE_1 * @QUANTITY

		SET	@TOTAL_AMT_2 = @UNIT_PRICE_2 * @QUANTITY

		SET	@TOTAL_AMT_3 = @UNIT_PRICE_3 * @QUANTITY

		SET @TOTAL_AMT_4 = @UNIT_PRICE_4 * @QUANTITY

		SET @TOTAL_AMT_5 = @UNIT_PRICE_5 * @QUANTITY

		INSERT INTO TR_REQ_DOC_XETGIA_DUOI_100M_DT

			([RECORD_DT_ID],[RECORD_MASTER_ID],[GOOD_NAME],[TECH_SPECIFICATION],[UNIT],[QUANTITY],

			[SUPPLIER_1],[UNIT_PRICE_1], [TOTAL_AMT_1], [VAT_AMT_1], [TOTAL_AMT_AFTER_VAT_1],[SUPPLIER_2],

			[UNIT_PRICE_2],[TOTAL_AMT_2], [VAT_AMT_2],[TOTAL_AMT_AFTER_VAT_2],[SUPPLIER_3],[UNIT_PRICE_3], 

			[TOTAL_AMT_3], [VAT_AMT_3],[TOTAL_AMT_AFTER_VAT_3], [SUPPLIER_4],[UNIT_PRICE_4], [TOTAL_AMT_4],

			[VAT_AMT_4],[TOTAL_AMT_AFTER_VAT_4], [SUPPLIER_5],[UNIT_PRICE_5], [TOTAL_AMT_5], [VAT_AMT_5],

			[TOTAL_AMT_AFTER_VAT_5],[NOTES], TR_REQ_DOC_DT_ID, WIN_SUPPLIER_PRICE, HH_ID)

		VALUES

			(@tr_ADDNEWDT_ID, @tr_ADDNEW_ID, @GOOD_NAME, @TECH_SPECIFICATION, @UNIT, @QUANTITY, 

			@SUPPLIER_1, @UNIT_PRICE_1, @TOTAL_AMT_1, @VAT_AMT_1, @TOTAL_AMT_AFTER_VAT_1,@SUPPLIER_2, 

			@UNIT_PRICE_2, @TOTAL_AMT_2, @VAT_AMT_2, @TOTAL_AMT_AFTER_VAT_2, @SUPPLIER_3, @UNIT_PRICE_3, 

			@TOTAL_AMT_3, @VAT_AMT_3, @TOTAL_AMT_AFTER_VAT_3, @SUPPLIER_4, @UNIT_PRICE_4, @TOTAL_AMT_4, 

			@VAT_AMT_4,@TOTAL_AMT_AFTER_VAT_4, @SUPPLIER_5, @UNIT_PRICE_5, @TOTAL_AMT_5, @VAT_AMT_5,

			@TOTAL_AMT_AFTER_VAT_5, @NOTES, @TR_REQ_DOC_DT_ID, @WIN_SUPPLIER_PRICE, @HH_ID)

		FETCH NEXT FROM AddnewDT INTO 

			@RECORD_MASTER_ID, 

			@GOOD_NAME,

			@HH_ID,

			@TECH_SPECIFICATION, 

			@UNIT, 

			@QUANTITY,

			@SUPPLIER_1, 

			@UNIT_PRICE_1,

			@TOTAL_AMT_1,

			@VAT_AMT_1,

			@TOTAL_AMT_AFTER_VAT_1,

			@SUPPLIER_2, 

			@UNIT_PRICE_2,

			@TOTAL_AMT_2,

			@VAT_AMT_2,

			@TOTAL_AMT_AFTER_VAT_2,

			@SUPPLIER_3, 

			@UNIT_PRICE_3,

			@TOTAL_AMT_3,

			@VAT_AMT_3,

			@TOTAL_AMT_AFTER_VAT_3,

			@SUPPLIER_4,

			@UNIT_PRICE_4,

			@TOTAL_AMT_4,

			@VAT_AMT_4,

			@TOTAL_AMT_AFTER_VAT_4,

			@SUPPLIER_5,

			@UNIT_PRICE_5,

			@TOTAL_AMT_5,

			@VAT_AMT_5,

			@TOTAL_AMT_AFTER_VAT_5,

			@NOTES, 

			@RECORD_DT_ID,

			@TR_REQ_DOC_DT_ID,

			@WIN_SUPPLIER_PRICE

		PRINT @tr_ADDNEWDT_ID

	END

	CLOSE AddnewDT

	DEALLOCATE AddnewDT

COMMIT TRANSACTION

SELECT '0' as Result, @tr_ADDNEW_ID  ADDNEW_ID, @p_REQ_CODE REQ_CODE, 

						N'Thêm mới biên bản xét giá thành công' ErrorDesc

RETURN '0'

ABORT:

BEGIN

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ADDNEW_ID, @p_REQ_CODE REQ_CODE,'' ErrorDesc

		RETURN '-1'

End

END TRY

BEGIN CATCH

		ROLLBACK TRANSACTION

		SELECT '-1' as Result, '' ADDNEW_ID, @p_REQ_CODE REQ_CODE,ERROR_MESSAGE() ErrorDesc

		RETURN '-1'

END CATCH