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

USE gAMSPro_BVB_v3_FINAL

GO

-- =============================================

-- Author:		TuNT

-- Create date: 20/11/2020

-- Description:	Store insert tai san kiem ke

-- =============================================

ALTER PROCEDURE dbo.ASS_INVENTORY_DETAIL_Ins 

		@p_INVENTDT_ID VARCHAR(15),

		@p_INVENT_ID	varchar(15)  ,

		@p_ASSET_ID	varchar(15)  ,

		@p_ASSET_STATUS	nvarchar(20) ,

		@p_INVENT_DESC	nvarchar(1000) ,

		@p_NOTES	nvarchar(500) ,

		@p_BRANCH_USE	varchar(15) ,

		@p_DEPT_USE	varchar(15),

		@p_REMAIN_VALUE decimal(18,0),

		@p_INVENTORY_DATE varchar(25)

AS

BEGIN

	--DECLARE @l_INVENTDT_ID VARCHAR(15)

	IF NOT EXISTS (SELECT * FROM ASS_INVENTORY_DT_RPT WHERE INVENT_ID = @p_INVENT_ID)

	AND NOT EXISTS(SELECT * FROM ASS_INVENTORY_DT WHERE ASSET_ID = @p_ASSET_ID AND @p_INVENT_ID = INVENT_ID)

	AND NOT EXISTS(SELECT * FROM ASS_MASTER WHERE ASSET_ID = @p_ASSET_ID)

	BEGIN

		SELECT '-1' as Result, '' id, N'Mã tài sản không có trong hệ thống' ErrorDesc

		RETURN '-1'

	END

	--IF EXISTS (SELECT * FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID

	--				AND BRANCH_ID = @p_BRANCH_USE

	--				AND (DEPT_ID = @p_DEPT_USE OR (ISNULL(DEPT_ID,'') = '' AND ISNULL(@p_DEPT_USE,'') = '')))

	--	AND EXISTS (SELECT * FROM ASS_STATUS WHERE STATUS_ID = @p_ASSET_STATUS AND STATUS_CODE = '05')

	--BEGIN

	--	SELECT '-1' as Result, '' id, N'Không được phép cập nhật trạng thái Thừa so với sao kê đối với tài sản thuộc danh mục kiểm kê' ErrorDesc

	--	RETURN '-1'

	--END

	DECLARE @l_TERM VARCHAR(20), @l_INVENTORY_DT DATE, @l_ASSET_TYPE VARCHAR(10), @l_BRANCH_ID VARCHAR(20), @l_DEPT_ID VARCHAR(20)

	DECLARE @AMORTIZED_AMT VARCHAR(50)

	SELECT @l_ASSET_TYPE = [TYPE_ID] FROM ASS_MASTER WHERE ASSET_ID = @p_ASSET_ID

	SELECT @l_TERM = TERM, @l_INVENTORY_DT = INVENTORY_DT, @l_BRANCH_ID = BRANCH_ID, @l_DEPT_ID = DEPT_ID

	FROM ASS_INVENTORY_MASTER WHERE INVENT_ID = @p_INVENT_ID

	--SET @p_INVENT_ID = (SELECT TOP(1) INVENT_ID FROM ASS_INVENTORY_MASTER

	--					WHERE BRANCH_ID = @l_BRANCH_ID

	--					AND (DEPT_ID = @l_DEPT_ID OR (ISNULL(DEPT_ID, '') = '' AND ISNULL(@l_DEPT_ID,'') = ''))

	--					AND TERM = @l_TERM

	--					AND ASSET_TYPE = @l_ASSET_TYPE

	--					AND INVENTORY_DT = @l_INVENTORY_DT

	--					AND RECORD_STATUS = '1'

	--					AND AUTH_STATUS = 'U')

	--IF(@p_INVENT_ID IS NULL OR @p_INVENT_ID = '')

	--BEGIN

	--	SELECT '-1' as Result, '' id, N'Tài sản không thuộc kỳ kiểm nào' ErrorDesc

	--	RETURN '-1'

	--END

	DECLARE @p_INVENT_ID_Tmp VARCHAR(15)

	SELECT @p_INVENT_ID_Tmp = aidr.INVENT_ID FROM ASS_INVENTORY_DT_RPT aidr WHERE aidr.INVENTDT_ID = @p_INVENTDT_ID

	--ThuanTm cmt: tài sản nằm ngoài đợt kk được phép lưu

	--IF (@p_INVENT_ID_Tmp IS NOT NULL AND @p_INVENT_ID_Tmp <> '' AND @p_INVENT_ID_Tmp <> @p_INVENT_ID AND

	--	EXISTS(SELECT * FROM ASS_INVENTORY_MASTER aim WHERE aim.INVENT_ID = @p_INVENT_ID_Tmp AND aim.AUTH_STATUS <> 'A' OR aim.AUTH_STATUS IS NULL ))

	-- BEGIN

	--	DECLARE @BRANCH_NAME NVARCHAR(50)

	--	SELECT @BRANCH_NAME = cb.BRANCH_CODE + ' - ' + cb.BRANCH_NAME FROM ASS_INVENTORY_MASTER aim

	--	LEFT JOIN CM_BRANCH cb ON aim.BRANCH_ID = cb.BRANCH_ID WHERE aim.INVENT_ID = @p_INVENT_ID_Tmp AND aim.AUTH_STATUS <> 'A' OR aim.AUTH_STATUS IS NULL

	--		SELECT '-1' as Result, '' id, N'Tài sản đang thuộc về kỳ kiểm kê của' + @BRANCH_NAME + N'. Bạn không được phép cập nhật thông tin tài sản này' ErrorDesc

	--		RETURN '-1'

	--END

	BEGIN TRANSACTION

		IF EXISTS (SELECT * FROM ASS_INVENTORY_DT_RPT WHERE INVENT_ID = @p_INVENT_ID)

		BEGIN

			IF EXISTS(SELECT * FROM ASS_INVENTORY_DT_RPT A

						LEFT JOIN ASS_INVENTORY_MASTER B ON A.INVENT_ID = B.INVENT_ID

						WHERE (@p_INVENTDT_ID = A.INVENTDT_ID OR (A.ASSET_ID = @p_ASSET_ID AND B.INVENT_ID = @p_INVENT_ID AND A.BRANCH_USE = B.BRANCH_ID)))

			BEGIN

				--SELECT TOP(1) @l_INVENTDT_ID = INVENTDT_ID FROM ASS_INVENTORY_DT_RPT WHERE ASSET_ID = @p_ASSET_ID AND @p_INVENT_ID = INVENT_ID

				IF (@p_INVENTDT_ID IS NOT NULL OR @p_INVENTDT_ID <> '')

				BEGIN

					UPDATE ASS_INVENTORY_DT_RPT 

					SET ASSET_STATUS = @p_ASSET_STATUS,

					INVENT_DESC = @p_INVENT_DESC,

					NOTES = @p_NOTES,

					BRANCH_USE = @p_BRANCH_USE,

					DEPT_USE = @p_DEPT_USE,

					REMAIN_VALUE = @p_REMAIN_VALUE,

					INVENTORY_DATE = CONVERT(datetime,@p_INVENTORY_DATE,103)

					WHERE @p_INVENTDT_ID = INVENTDT_ID

				END

				ELSE

				BEGIN

					SET @p_INVENTDT_ID = (SELECT INVENTDT_ID FROM ASS_INVENTORY_DT_RPT WHERE ASSET_ID = @p_ASSET_ID AND INVENT_ID = @p_INVENT_ID)

					UPDATE ASS_INVENTORY_DT_RPT 

					SET ASSET_STATUS = @p_ASSET_STATUS,

					INVENT_DESC = @p_INVENT_DESC,

					NOTES = @p_NOTES,

					BRANCH_USE = @p_BRANCH_USE,

					DEPT_USE = @p_DEPT_USE,

					REMAIN_VALUE = @p_REMAIN_VALUE,

					INVENTORY_DATE = CONVERT(datetime,@p_INVENTORY_DATE,103)

					WHERE ASSET_ID = @p_ASSET_ID AND INVENT_ID = @p_INVENT_ID

				END

			END

			ELSE

			BEGIN

				SELECT @p_BRANCH_USE = am.BRANCH_ID, @p_DEPT_USE = am.DEPT_ID, @p_REMAIN_VALUE = am.BUY_PRICE - am.AMORTIZED_AMT, @AMORTIZED_AMT = CONVERT(VARCHAR,am.AMORTIZED_AMT) FROM ASS_MASTER am WHERE am.ASSET_ID = @p_ASSET_ID

				EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @p_INVENTDT_ID OUT

				INSERT INTO ASS_INVENTORY_DT_RPT(INVENTDT_ID, INVENT_ID, ASSET_ID, ASSET_STATUS, INVENT_DESC, NOTES, BRANCH_USE, DEPT_USE, REMAIN_VALUE,INVENTORY_DATE)

				VALUES(@p_INVENTDT_ID, @p_INVENT_ID,@p_ASSET_ID, @p_ASSET_STATUS, @p_INVENT_DESC, @p_NOTES, @p_BRANCH_USE, @p_DEPT_USE, @p_REMAIN_VALUE,CONVERT(datetime,@p_INVENTORY_DATE,103))

				--SELECT * FROM ASS_INVENTORY_DT

			END

		END

		ELSE 

		BEGIN

			IF EXISTS(SELECT * FROM ASS_INVENTORY_DT A

						LEFT JOIN ASS_INVENTORY_MASTER B ON A.INVENT_ID = B.INVENT_ID

						WHERE (@p_INVENTDT_ID = A.INVENTDT_ID OR (A.ASSET_ID = @p_ASSET_ID AND B.INVENT_ID = @p_INVENT_ID AND A.BRANCH_USE = B.BRANCH_ID)))

			BEGIN

				IF (@p_INVENTDT_ID IS NOT NULL OR @p_INVENTDT_ID <> '')

				BEGIN

					UPDATE ASS_INVENTORY_DT 

					SET ASSET_STATUS = @p_ASSET_STATUS,

					INVENT_DESC = @p_INVENT_DESC,

					NOTES = @p_NOTES,

					BRANCH_USE = @p_BRANCH_USE,

					DEPT_USE = @p_DEPT_USE,

					REMAIN_VALUE = @p_REMAIN_VALUE

					WHERE @p_INVENTDT_ID = INVENTDT_ID

				END

				ELSE

				BEGIN

					SET @p_INVENTDT_ID = (SELECT INVENTDT_ID FROM ASS_INVENTORY_DT WHERE ASSET_ID = @p_ASSET_ID AND INVENT_ID = @p_INVENT_ID)

					UPDATE ASS_INVENTORY_DT

					SET ASSET_STATUS = @p_ASSET_STATUS,

					INVENT_DESC = @p_INVENT_DESC,

					NOTES = @p_NOTES,

					BRANCH_USE = @p_BRANCH_USE,

					DEPT_USE = @p_DEPT_USE,

					REMAIN_VALUE = @p_REMAIN_VALUE

					WHERE ASSET_ID = @p_ASSET_ID AND INVENT_ID = @p_INVENT_ID

				END

			END

			ELSE

			BEGIN

				--DECLARE @AMORTIZED_AMT VARCHAR(50)

				SELECT @p_BRANCH_USE = am.BRANCH_ID, @p_DEPT_USE = am.DEPT_ID, @p_REMAIN_VALUE = am.BUY_PRICE - am.AMORTIZED_AMT, @AMORTIZED_AMT = CONVERT(VARCHAR,am.AMORTIZED_AMT) FROM ASS_MASTER am WHERE am.ASSET_ID = @p_ASSET_ID

				EXEC SYS_CodeMasters_Gen 'ASS_INVENTORY_DT', @p_INVENTDT_ID OUT

				INSERT INTO ASS_INVENTORY_DT(INVENTDT_ID, INVENT_ID, ASSET_ID, ASSET_STATUS, INVENT_DESC, NOTES, BRANCH_USE, DEPT_USE, REMAIN_VALUE)

				VALUES(@p_INVENTDT_ID, @p_INVENT_ID,@p_ASSET_ID, @p_ASSET_STATUS, @p_INVENT_DESC, @p_NOTES, @p_BRANCH_USE, @p_DEPT_USE, @p_REMAIN_VALUE)

				--SELECT * FROM ASS_INVENTORY_DT

			END

		END

		IF @@Error <> 0 GOTO ABORT

	COMMIT TRANSACTION

	SELECT '0' as Result, @p_INVENTDT_ID  id, @p_INVENT_ID  INVENT_ID, '' ErrorDesc

	RETURN '0'

	ABORT:

	BEGIN

			ROLLBACK TRANSACTION

			SELECT '-1' as Result, '' id, '' ErrorDesc

			RETURN '-1'

	End

END

GO