ALTER   PROC dbo.ASS_COLLECT_MULTI_MASTER_BBBG
@COL_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_RECEVI_MAKER_ID VARCHAR(50),@SIGN_RECEVI_DT DATETIME,@SIGN_APPROVE_ID VARCHAR(20),@SIGN_APPROVE_DT DATETIME,
		@SIGN_MAKER_ID_KT VARCHAR(20),@SIGN_MAKER_DT_KT DATETIME

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

-- Thông tin Ngày thu hồi
DECLARE @COLLECT_DT DATETIME, @CREATE_DT_COLLECT DATETIME
	SELECT  @COLLECT_DT=COLLECT_DT,@CREATE_DT_COLLECT =CREATE_DT FROM dbo.ASS_COLLECT_MULTI_MASTER WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
PRINT @COLLECT_DT

-- BÊN GIAO

-- 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), @CREATE_DT NVARCHAR(500)
-- BÊN GIAO

	-- Thông tin chữ kí
	SET  @SIGN_MAKER_ID =  ( SELECT MAKER_ID  FROM dbo.ASS_COLLECT_CONFIRM_MASTER A WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET  @SIGN_CREATE_DT =  ( SELECT A.CONFIRM_DT FROM ASS_COLLECT_CONFIRM_MASTER A WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)

	-- Thông tin bên giao (lấy thông tin người xác nhận)
	SET @TRANSFER_MAKER_ID = (SELECT MAKER_ID FROM ASS_COLLECT_CONFIRM_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @BRANCH_TRANSFER = ISNULL((SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID =(SELECT TLSUBBRID FROM TL_USER WHERE TLNANME =@TRANSFER_MAKER_ID)), '')
		+ ISNULL((SELECT ' - ' + DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID =(SELECT SECUR_CODE FROM TL_USER WHERE 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_COLLECT_MULTI_MASTER WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID) AS VARCHAR(500))
				+ N' tháng ' + CAST((SELECT MONTH(APPROVE_DT_KT) FROM dbo.ASS_COLLECT_MULTI_MASTER WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID) AS VARCHAR(500))
				+ N' năm ' + CAST((SELECT YEAR(APPROVE_DT_KT) FROM dbo.ASS_COLLECT_MULTI_MASTER WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID) AS VARCHAR(500))
				+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_COLLECT_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)


-- BÊN NHẬN
	-- Thông tin chữ kí
	SET  @SIGN_RECEVI_MAKER_ID =  ( SELECT MAKER_ID  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET  @SIGN_RECEVI_DT =  ( SELECT CREATE_DT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	-- Thông tin bên nhận
	SET @SIGN_APPROVE_ID =(SELECT TOP 1 CHECKER_ID FROM ASS_COLLECT_MULTI_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @SIGN_APPROVE_DT =(SELECT TOP 1 APPROVE_DT FROM ASS_COLLECT_MULTI_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @RECIVE_MAKER_ID = (SELECT TOP 1 MAKER_ID FROM ASS_COLLECT_MULTI_MASTER WHERE  COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
	SET @BRANCH_RECIVE = ISNULL((SELECT BRANCH_NAME FROM CM_BRANCH WHERE BRANCH_ID = (SELECT TOP 1 TLSUBBRID FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)), '')
	+ ISNULL((SELECT ' - ' + DEP_NAME FROM CM_DEPARTMENT WHERE DEP_ID =(SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_ID)), '')
	SET @DEP_RECIVE = (SELECT TOP 1 SECUR_CODE FROM TL_USER WHERE TLNANME = @RECIVE_MAKER_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_KT =  ( SELECT A.MAKER_ID_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
SET  @SIGN_MAKER_DT_KT =  ( SELECT A.CREATE_DT_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
SET  @SIGN_CHECKER_ID_KT =  ( SELECT CHECKER_ID_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
SET  @SIGN_APPROVE_DT_KT =  ( SELECT APPROVE_DT_KT  FROM ASS_COLLECT_MULTI_MASTER A WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)

-- DANH SÁCH TÀI SẢN
SELECT ROW_NUMBER()OVER(ORDER BY A.ASSET_ID) AS STT, A.COLLECT_NOTE AS NOTES,convert(varchar, A.COLLECT_DT, 103) as COLLECT_DT, B.ASSET_NAME, B.ASSET_DESC, B.[TYPE_ID],B.AMORT_START_DATE,B.BRANCH_ID AS BRANCH_USE,
	BRU.BRANCH_CODE AS BRANCH_CODE_USE, (BRU.BRANCH_NAME +' '+ ISNULL(DEP.DEP_NAME,'')) AS BRANCH_NAME_USE,B.EMP_ID, I.TLFullName AS EMP_NAME,
	H.TYPE_CODE, H.TYPE_NAME, 
	B.[GROUP_ID], G.GROUP_CODE, G.GROUP_NAME, 
	B.AMORT_AMT, CAST(B.AMORT_MONTH AS INT) AS AMORT_MONTH,
	C.BRANCH_CODE AS BRANCH_CODE_CREATE, C.BRANCH_NAME AS BRANCH_NAME_CREATE,--D.DEP_CODE,E.DIV_CODE, E.DIV_NAME, E.ADDR AS DIV_ADDR, 
	F.AUTH_STATUS_NAME,CONVERT(VARCHAR(10),A.CREATE_DT,110) AS NGAY_TAO,
	E.STATUS_NAME ASS_STATUS_NAME, --Tinh trang tai san
	D.STATUS_NAME AMORT_STATUS_NAME, 1 SOLUONG, B.ASSET_SERIAL_NO AS SERIAL,
--	FORMAT(B.BUY_PRICE, '#,#') AS BUY_PRICE
  FORMAT(tmp.BUY_PRICE, '#,#') AS BUY_PRICE
  ,B.ASSET_SERIAL_NO,
--  FORMAT(B.AMORT_AMT - B.AMORTIZED_AMT, '#,#') AS GT_CL,
  FORMAT(tmp.BUY_PRICE - ISNULL(tmp1.AMORTIZED_AMT,0) , '#,#') AS GT_CL,  --So tien khau hao con lai
  --CAST(B.AMORTIZED_MONTH AS INT) AS AMORTIZED_MONTH,
  ISNULL(CAST(tmp2.AMORTIZED_MONTH AS INT),0) AS AMORTIZED_MONTH,
  B.CREATE_DT AS CREATE_DT_ASS,B.USE_DATE,
	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
	--I.TLFullName
	FROM ASS_COLLECT_MULTI_DT A
	LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
	LEFT JOIN CM_BRANCH C ON A.BRANCH_ID = C.BRANCH_ID
	LEFT JOIN CM_BRANCH BRU ON BRU.BRANCH_ID = B.BRANCH_ID
	LEFT JOIN CM_DEPARTMENT DEP ON B.DEPT_ID = DEP.DEP_ID
	--LEFT JOIN CM_DIVISION E ON A.DIVISION_ID = E.DIV_ID
	LEFT JOIN CM_AUTH_STATUS F ON A.AUTH_STATUS = F.AUTH_STATUS
	LEFT JOIN ASS_GROUP G ON B.GROUP_ID = G.GROUP_ID
	LEFT JOIN ASS_TYPE H ON B.TYPE_ID = H.TYPE_ID
	LEFT JOIN TL_USER I ON I.TLNANME = B.EMP_ID
	LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
	LEFT JOIN ASS_AMORT_STATUS D ON B.AMORT_STATUS = D.STATUS_CODE
   --  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 < @COLLECT_DT AND  ASSET_AMT IS NOT NULL   THEN ASSET_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND TRN_DT < @COLLECT_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 < @COLLECT_DT AND AMORT_AMT IS NOT NULL THEN AMORT_AMT END) - ISNULL(SUM(CASE WHEN CRDR='D' AND AMORT_DT < @COLLECT_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<@COLLECT_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<@COLLECT_DT GROUP BY ASSET_ID) tmp2  ON tmp2.ASSET_ID = A.ASSET_ID
  --    END
WHERE  COL_MULTI_MASTER_ID= @COL_MULTI_MASTER_ID

IF(CONVERT(DATE,@CREATE_DT_COLLECT,103)>CONVERT(DATE,@COLLECT_DT,103))
BEGIN
	SET @CREATE_DT = N'Ngày ' + CAST(DAY(@COLLECT_DT) AS VARCHAR(500))
				+ N' tháng ' + CAST(MONTH(@COLLECT_DT) AS VARCHAR(500))
				+ N' năm ' + CAST(YEAR(@COLLECT_DT) AS VARCHAR(500))
				+ N' tại ' + (SELECT B.BRANCH_NAME FROM dbo.ASS_COLLECT_MULTI_MASTER A JOIN dbo.CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID)
  IF(@SIGN_APPROVE_DT_KT IS NOT NULL)
  BEGIN
  	SET @SIGN_APPROVE_DT    = @COLLECT_DT
  	SET @SIGN_APPROVE_DT_KT = @COLLECT_DT
  	SET @SIGN_RECEVI_DT     = @COLLECT_DT
    SET @SIGN_CREATE_DT     = @COLLECT_DT
  END
END


-- THÔNG TIN BÊN GIAO
SELECT @KHOI_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 @KHOI_RECIVE AS KHOI_RECIVE, @BRANCH_RECIVE AS BRANCH_RECIVE, @NAME_RECIVE AS NAME_RECIVE, @POSITION_RECIVE AS POSITION_RECIVE

-- CHỮ KÍ
DECLARE @S1 NVARCHAR(500),@S2 NVARCHAR(500),@S3 NVARCHAR(500),@S4 NVARCHAR(500),@S5 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 = (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'),'')

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

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

SELECT @S1 AS S1,
@S2 AS S2,
@S3 AS S3,IIF(EXISTS(SELECT 1 FROM dbo.ASS_COLLECT_MULTI_MASTER WHERE COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID AND AUTH_STATUS='E'),NULL,@S4) AS S4, @S5 AS S5