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

ALTER   PROC dbo.ASS_TRANSFER_MULTI_MASTER_BBBG

@TRANS_MULTI_MASTER_ID	varchar(15) = null

AS 

-- Thông tin chứ kí

DECLARE @SIGN_CHECKER_ID_KT VARCHAR(20),@SIGN_MAKER_ID VARCHAR(20),@SIGN_APPROVE_DT_KT DATETIME,@SIGN_CREATE_DT DATETIME,@SIGN_APPROVE_ID VARCHAR(20),@SIGN_APPROVE_DT DATETIME,

		@SIGN_RECEVI_MAKER_ID VARCHAR(50),@SIGN_RECEVI_DT DATETIME,@SIGN_MAKER_ID_KT VARCHAR(50),@SIGN_MAKER_ID_CREATE VARCHAR(50),@AUTH_STATUS VARCHAR(50)

-- 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 điều chuyển

DECLARE @TRANSFER_DT DATETIME, @CREATE_DT_TRANSFER DATETIME

	SELECT  @TRANSFER_DT=TRANSFER_DT,@CREATE_DT_TRANSFER =CREATE_DT FROM dbo.ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID

-- BÊN GIAO

	-- Thông tin chữ kí

	SET  @SIGN_MAKER_ID =  ( SELECT MAKER_ID_BG  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET  @SIGN_CREATE_DT = ( SELECT CONFIRM_DT_BG  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @SIGN_APPROVE_ID = (SELECT CHECKER_ID FROM ASS_TRANSFER_MULTI_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @SIGN_APPROVE_DT = (SELECT APPROVE_DT FROM ASS_TRANSFER_MULTI_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	-- Thông tin bên giao

	SET @TRANSFER_MAKER_ID = (SELECT MAKER_ID_BG FROM ASS_TRANSFER_CONFIRM_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @BRANCH_TRANSFER = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

		+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID_OLD WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	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_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID) AS VARCHAR(500))

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

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

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

-- BÊN NHẬN

	-- Thông tin chữ kí

	SET  @SIGN_RECEVI_MAKER_ID =  ( SELECT MAKER_ID_BN  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET  @SIGN_RECEVI_DT =  ( SELECT CONFIRM_DT_BN  FROM ASS_TRANSFER_CONFIRM_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

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

	SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID_BN FROM ASS_TRANSFER_CONFIRM_MASTER WHERE  TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

	SET @BRANCH_RECIVE = ISNULL((SELECT TOP 1 A.BRANCH_NAME FROM dbo.CM_BRANCH A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.BRANCH_ID = B.BRANCH_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

		+ ISNULL((SELECT TOP 1 ' - ' + A.DEP_NAME FROM dbo.CM_DEPARTMENT A JOIN dbo.ASS_TRANSFER_MULTI_DT B ON A.DEP_ID = B.DEPT_ID WHERE B.TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID ORDER BY B.TRANSFER_MULTI_ID ASC), '')

	SET @DEP_RECIVE = (SELECT TOP 1 DEPT_ID FROM ASS_TRANSFER_MULTI_DT WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

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

	SET @POSITION_RECIVE =  (SELECT POS_NAME FROM CM_EMPLOYEE_LOG WHERE USER_DOMAIN = @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_MAKER_ID_CREATE =  ( SELECT A.MAKER_ID  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_CHECKER_ID_KT =  ( SELECT CHECKER_ID_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_APPROVE_DT_KT =  ( SELECT APPROVE_DT_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

SET  @SIGN_MAKER_ID_KT	 =  ( SELECT A.MAKER_ID_KT  FROM ASS_TRANSFER_MULTI_MASTER A WHERE TRANS_MULTI_MASTER_ID = @TRANS_MULTI_MASTER_ID)

-- DANH SÁCH TÀI SẢN

SELECT	ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT,

				A.ASSET_ID, A.[TYPE_ID],C.[TYPE_CODE],  C.[TYPE_NAME], A.GROUP_ID,B.GROUP_CODE, B.GROUP_NAME, A.ASSET_NAME, A.ASSET_DESC,

				A.SUP_ID, FORMAT(tmp.BUY_PRICE, '#,#') AS BUY_PRICE, A.AMORT_AMT, A.ASS_TYPE, TR.BRANCH_ID,BR_R.BRANCH_CODE,BR_R.BRANCH_NAME,H.DEP_ID, H.DEP_CODE,

				H.DEP_NAME, A.EMP_ID,EM_R.EMP_CODE,EM_R.EMP_NAME,

				A.BUY_DATE, A.USE_DATE, A.SPECIAL_ASS, CAST(A.AMORT_MONTH AS INT) AS AMORT_MONTH, A.AMORT_RATE, A.AMORT_START_DATE, A.AMORT_END_DATE,

				A.FIRST_AMORT_AMT, 

				--CAST(A.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH, 

				CAST((A.AMORT_MONTH - A.AMORTIZED_MONTH) AS INT) AS REMAIN_MONTH,

--				CASE WHEN DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT) > A.AMORT_MONTH THEN  CAST(A.AMORT_MONTH AS INT) ELSE CAST(DATEDIFF(month,A.AMORT_START_DATE,@TRANSFER_DT)AS INT) END AS AMORTIZED_MONTH,

				--CAST(DATEDIFF(month,A.AMORT_START_DATE,tmp2.AMORT_DT)AS INT)  AS AMORTIZED_MONTH,

        ISNULL(CAST(tmp2.AMORTIZED_MONTH AS INT),0) AS AMORTIZED_MONTH,

				A.AMORTIZED_AMT, 

				FORMAT(tmp.BUY_PRICE - ISNULL(tmp1.AMORTIZED_AMT,0) , '#,#') AS GT_CL,  --So tien khau hao con lai

				A.PO_ID, A.WAREHOUSE_ID, A.LOCATION, A.REF_ASSET_ID,

				A.REF_AMORTIZED_AMT,

				A.WARRANTY_MONTHS, 				

				A.AMORT_STATUS, 

				D.STATUS_NAME AMORT_STATUS_NAME, 

				A.ASS_STATUS, 

				E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san

				A.ASS_STATUS_DESC,

				A.RECORD_STATUS, 

				A.AUTH_STATUS, 

				ZZ.AUTH_STATUS_NAME,

				A.MAKER_ID, 

				convert(varchar, A.CREATE_DT, 103) as CREATE_DT_ASS,

				convert(varchar, TR.CREATE_DT, 103) as CREATE_DT, 

				A.CHECKER_ID, 

				A.APPROVE_DT,

				TR.[DESCRIPTION], TR.BRANCH_CREATE,TR.LOCATION,TR.[DESCRIPTION],TR.TRANSFER_MULTI_ID,

				A.AMORT_AMT-A.AMORTIZED_AMT ASREMAIN_AMT, convert(varchar, B.CREATE_DT, 103) as CREATE_DT_ASS, 

				1 AS SOLUONG,

				TR.DESCRIPTION AS NOTES,

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

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

					ELSE (A.ASSET_CODE) END AS ASSET_CODE

		FROM [dbo].[ASS_TRANSFER_MULTI_DT] TR

		LEFT JOIN ASS_MASTER A ON A.ASSET_ID = TR.ASSET_ID

		LEFT JOIN ASS_GROUP B ON A.GROUP_ID = B.GROUP_ID

		LEFT JOIN ASS_TYPE C ON A.[TYPE_ID] = C.[TYPE_ID]

		LEFT JOIN ASS_AMORT_STATUS D ON A.AMORT_STATUS = D.STATUS_CODE

		LEFT JOIN ASS_STATUS E ON A.ASS_STATUS = E.STATUS_ID

		LEFT JOIN CM_AUTH_STATUS ZZ ON ZZ.AUTH_STATUS = TR.AUTH_STATUS

		LEFT JOIN CM_BRANCH BR_R ON BR_R.BRANCH_ID=TR.BRANCH_ID

		LEFT JOIN CM_EMPLOYEE EM_R ON EM_R.EMP_ID=TR.EMP_ID

		LEFT JOIN CM_DEPARTMENT H ON H.DEP_ID = TR.DEPT_ID

    --  PHONGNT 4/9/22 LẤY GIÁ TRỊ NGUYÊN GIÁ, KHẤU HAO TẠI THỜI ĐIỂM NGÀY ĐIỀU CHUYỂN

		LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND TRN_DT < @TRANSFER_DT AND  ASSET_AMT IS NOT NULL   THEN ASSET_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND TRN_DT < @TRANSFER_DT AND  ASSET_AMT IS NOT NULL  THEN ASSET_AMT END),0)) AS BUY_PRICE FROM dbo.ASS_VALUES GROUP BY ASSET_ID) tmp ON A.ASSET_ID=tmp.ASSET_ID

		LEFT JOIN (SELECT ASSET_ID,(SUM(CASE WHEN CRDR='C' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND AMORT_DT < @TRANSFER_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END),0)) AS AMORTIZED_AMT FROM dbo.ASS_AMORT_DT GROUP BY ASSET_ID) tmp1 ON A.ASSET_ID=tmp1.ASSET_ID

--    LEFT JOIN (SELECT ASSET_ID,max(AMORT_DT) AS AMORT_DT FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2  ON tmp2.ASSET_ID = A.ASSET_ID

    LEFT JOIN (SELECT ASSET_ID,COUNT(*) AS AMORTIZED_MONTH FROM ASS_AMORT_DT WHERE CRDR ='C' AND AMORT_DT<@TRANSFER_DT GROUP BY ASSET_ID) tmp2  ON tmp2.ASSET_ID = A.ASSET_ID

    --    END

WHERE 1=1 

AND TR.TRANS_MULTI_MASTER_ID= @TRANS_MULTI_MASTER_ID

IF(CONVERT(DATE,@CREATE_DT_TRANSFER,103)>CONVERT(DATE,@TRANSFER_DT,103))

BEGIN

	SET @CREATE_DT = N'Ngày ' + CAST(DAY(@TRANSFER_DT) AS VARCHAR(500))

				+ N' tháng ' + CAST(MONTH(@TRANSFER_DT) AS VARCHAR(500))

				+ N' năm ' + CAST(YEAR(@TRANSFER_DT) AS VARCHAR(500))

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

  IF(@SIGN_APPROVE_DT_KT IS NOT NULL)

  BEGIN

  	SET @SIGN_APPROVE_DT = @TRANSFER_DT

  	SET @SIGN_APPROVE_DT_KT = @TRANSFER_DT

  	SET @SIGN_RECEVI_DT = @TRANSFER_DT

    SET @SIGN_CREATE_DT = @TRANSFER_DT

  END

END

-- 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

-- THÔNG TIN BÊN NHẬN

SELECT IIF(@NAME_RECIVE IS NOT NULL,ISNULL(@KHOI_RECIVE,@BRANCH_RECIVE),NULL) AS KHOI_RECIVE, IIF(@NAME_RECIVE IS NOT NULL,@BRANCH_RECIVE,NULL) 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),@S6 NVARCHAR(500)

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

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

--PHONGNT bổ sung người nhập KT

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

SET @S6 =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'),'')

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

--PHONGNT bổ sung người nhập 

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

SELECT @S1 AS S1,

@S2 AS S2,

@S3 AS S3,@S4 AS S4,IIF(NOT EXISTS(SELECT 1 FROM ASS_TRANSFER_MULTI_MASTER WHERE TRANS_MULTI_MASTER_ID=@TRANS_MULTI_MASTER_ID AND AUTH_STATUS ='E'),@S5,NULL) AS S5,@S6 AS S6