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

ALTER   PROC dbo.ASS_USE_MULTI_BVB_MASTER_BBBG

@p_USER_MASTER_ID VARCHAR(15)

AS

	-- Thông tin chứ kí

	DECLARE @SIGN_CHECKER_ID_KT VARCHAR(20),@SIGN_HANDOVER_ID VARCHAR(20),@SIGN_APPROVE_DT_KT DATETIME,@SIGN_HANDOVER_DT DATETIME,

	@SIGN_RECEVER_ID VARCHAR(50),@SIGN_RECEVER_DT DATETIME, @SIGN_CHECKER_ID VARCHAR(20), @SIGN_APPROVE_DT DATETIME,@SIGN_MAKER_ID VARCHAR(20),@SIGN_MAKER_ID_KT VARCHAR(20),@SIGN_CREATE_ID_KT VARCHAR(20)

	-- Thông tin bên giao

	DECLARE @DEP_TRANSFER VARCHAR(15),  @BRANCH_TRANSFER NVARCHAR(500),@NAME_TRANSFER NVARCHAR(500), @POSITION NVARCHAR(500),@KHOI_TRANSFER NVARCHAR(500),

	@TRANSFER_MAKER_ID VARCHAR(20), @CREATE_DT NVARCHAR(500)

	-- Thông tin bên nhận

	DECLARE @DEP_RECIVE VARCHAR(15),  @BRANCH_RECIVE NVARCHAR(500),@NAME_RECIVE NVARCHAR(500), @POSITION_RECIVE NVARCHAR(500),@KHOI_RECIVE NVARCHAR(500),

	@RECIVE_MAKER_ID VARCHAR(20)

  -- Thông tin Ngày xuất sử dụng 

  DECLARE @USE_EXPORT_DT DATETIME, @USE_CREATE_DT DATETIME

	SELECT @USE_EXPORT_DT=USE_EXPORT_DT,@USE_CREATE_DT =CREATE_DT FROM dbo.ASS_USE_MULTI_MASTER WHERE  USER_MASTER_ID = @p_USER_MASTER_ID

	-- BÊN GIAO

		-- Thông tin chữ ký

		SET @SIGN_HANDOVER_ID = (SELECT A.MAKER_ID FROM dbo.ASS_USE_MULTI_MASTER A WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @SIGN_HANDOVER_DT =	(SELECT A.CREATE_DT  FROM ASS_USE_MULTI_MASTER A WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID)

		-- Thông tin bên giao

		SET @TRANSFER_MAKER_ID = (SELECT MAKER_ID FROM ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @BRANCH_TRANSFER = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.TL_USER B ON A.BRANCH_ID = B.TLSUBBRID WHERE B.TLNANME = @TRANSFER_MAKER_ID), '')

			+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.TL_USER B ON A.DEP_ID = B.SECUR_CODE WHERE B.TLNANME = @TRANSFER_MAKER_ID), '')

		SET @NAME_TRANSFER =(SELECT TLFullName FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID)

		SET @POSITION = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@TRANSFER_MAKER_ID)

		SET @DEP_TRANSFER =(SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @TRANSFER_MAKER_ID)

		SET @KHOI_TRANSFER= (SELECT TOP 1 DVDM_NAME FROM CM_DVDM WHERE DVDM_ID IN (SELECT DVDM_ID FROM PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM PL_COSTCENTER_DT

			WHERE DEP_ID = @DEP_TRANSFER )) AND IS_KHOI ='1')

		SET @CREATE_DT = N'Ngày ' + CAST((SELECT DAY(APPROVE_DT_KT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' tháng ' + CAST((SELECT MONTH(APPROVE_DT_KT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' năm ' + CAST((SELECT YEAR(APPROVE_DT_KT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_USE_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

	-- BÊN NHẬN

		-- Thông tin chữ ký

		SET @SIGN_RECEVER_ID = (SELECT A.MAKER_ID FROM dbo.ASS_USE_CONFIRM_MASTER A WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @SIGN_RECEVER_DT =	(SELECT A.CONFIRM_DT  FROM ASS_USE_CONFIRM_MASTER A WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID)

		-- Thông tin bên nhận

		SET @RECIVE_MAKER_ID = (SELECT A.MAKER_ID FROM dbo.ASS_USE_CONFIRM_MASTER A WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @BRANCH_RECIVE = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.TL_USER B ON A.BRANCH_ID = B.TLSUBBRID WHERE B.TLNANME = @RECIVE_MAKER_ID), '')

			+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.TL_USER B ON A.DEP_ID = B.SECUR_CODE WHERE B.TLNANME = @RECIVE_MAKER_ID), '')

		SET @NAME_RECIVE =(SELECT TLFullName FROM TL_USER WHERE TLNANME =@RECIVE_MAKER_ID)

		SET @POSITION_RECIVE = (SELECT TOP 1 POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN =@RECIVE_MAKER_ID)

		SET @DEP_RECIVE =(SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)

		SET @KHOI_RECIVE= (SELECT TOP 1 DVDM_NAME FROM CM_DVDM WHERE DVDM_ID IN (SELECT DVDM_ID FROM PL_COSTCENTER WHERE COST_ID IN (SELECT COST_ID FROM PL_COSTCENTER_DT

			WHERE DEP_ID = @DEP_RECIVE )) AND IS_KHOI ='1')

	-- KẾ TOÁN

		SET  @SIGN_CHECKER_ID_KT =  ( SELECT CHECKER_ID_KT  FROM ASS_USE_MULTI_MASTER A WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

		SET  @SIGN_APPROVE_DT_KT =  ( SELECT APPROVE_DT_KT  FROM ASS_USE_MULTI_MASTER A WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @SIGN_CHECKER_ID = (SELECT CHECKER_ID FROM ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

		SET @SIGN_APPROVE_DT = (SELECT APPROVE_DT FROM ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

		SET  @SIGN_CREATE_ID_KT =  ( SELECT A.MAKER_ID_KT  FROM ASS_USE_MULTI_MASTER A WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

	-- Danh sách biên bản bàn giao

	SELECT Row_number() over(order by B.ASSET_CODE) AS STT, B.ASSET_NAME, FORMAT(B.BUY_PRICE, '#,#') AS BUY_PRICE, CAST(B.AMORT_MONTH AS INT) AS AMORT_MONTH, 

	CAST(B.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH, B.AMORT_AMT, B.AMORTIZED_AMT, FORMAT(AMORT_AMT - B.AMORTIZED_AMT, '#,#') AS GT_CL,'' UNITNAME,1 AS SOLUONG ,

	A.NOTES AS NOTES,

	CASE WHEN B.ASSET_SERIAL_NO IS NOT NULL AND B.ASSET_SERIAL_NO <> ''

		THEN  B.ASSET_SERIAL_NO+CHAR(10)+'(' + B.ASSET_CODE + ')'

		ELSE (B.ASSET_CODE) END AS ASSET_CODE

	FROM dbo.ASS_USE_MULTI_DT A

	INNER JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID

	WHERE A.USER_MASTER_ID = @p_USER_MASTER_ID

	--PHONGNT 08/09/22 LUI NGAY TREN BB

	IF(CONVERT(VARCHAR,@USE_EXPORT_DT,103)<CONVERT(VARCHAR,@USE_CREATE_DT,103))

	BEGIN

		SET @CREATE_DT = N'Ngày ' + CAST((SELECT DAY(@USE_EXPORT_DT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' tháng ' + CAST((SELECT MONTH(@USE_EXPORT_DT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' năm ' + CAST((SELECT YEAR(@USE_EXPORT_DT) FROM dbo.ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID = @p_USER_MASTER_ID) AS VARCHAR(500))

					+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_USE_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE USER_MASTER_ID = @p_USER_MASTER_ID)

    IF(@SIGN_APPROVE_DT_KT IS NOT NULL)

    BEGIN

  		SET @SIGN_HANDOVER_DT = @USE_EXPORT_DT

  		SET @SIGN_APPROVE_DT_KT = @USE_EXPORT_DT

  		SET @SIGN_RECEVER_DT = @USE_EXPORT_DT

  		SET @SIGN_APPROVE_DT = @USE_EXPORT_DT

    END

	END

	-- BAT DAU SELECT Lấy thông tin bên giao

	SELECT ISNULL(@KHOI_TRANSFER,@BRANCH_TRANSFER) AS KHOI_TRANSFER, @BRANCH_TRANSFER AS BRANCH_TRANSFER, @NAME_TRANSFER AS NAME_TRANSFER, @POSITION AS POSITION_SEND, @CREATE_DT AS A1

	-- BAT DAU SELECT Lấy thông tin bên nhận

	SELECT ISNULL(@KHOI_RECIVE,@BRANCH_RECIVE) AS KHOI_RECIVE, @BRANCH_RECIVE AS BRANCH_RECIVE, @NAME_RECIVE AS NAME_RECIVE, @POSITION_RECIVE AS POSTION_RECIVE

	-- CHỮ KÍ

	DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 NVARCHAR(500)

		-- Bên giao

		SET @S1 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_HANDOVER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_HANDOVER_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

		-- Kế toán GDV

		SET @S3 = N'Người nhập'+CHAR(10)+(SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_CREATE_ID_KT) + CHAR(10)

		-- Kế toán KSV

		SET @S5 =N'Người duyệt'+CHAR(10)+(SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_CHECKER_ID_KT) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT_KT,'dd/MM/yyyy,hh:mm:ss tt'),'')

		-- Bên nhân

		SET @S4 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_RECEVER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_RECEVER_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

		-- QLTS

		SET @S2 = (SELECT TLFullName FROM TL_USER WHERE TLNANME = @SIGN_CHECKER_ID) + CHAR(10) + N' Đã xác nhận vào lúc ' + ISNULL(FORMAT(@SIGN_APPROVE_DT,'dd/MM/yyyy,hh:mm:ss tt'),'')

	SELECT IIF(NOT EXISTS(SELECT 1 FROM ASS_USE_MULTI_MASTER WHERE USER_MASTER_ID=@p_USER_MASTER_ID AND AUTH_STATUS ='E'),@S1,NULL) AS S1, @S2 AS S2, @S3 AS S3, @S4 AS S4, @S5 AS S5