ALTER     PROC dbo.ASS_USE_MULTI_HANDOVER_RECORD
@USER_MASTER_ID VARCHAR(15) = NULL
AS
BEGIN
--Table 0 Nội dung && BRANCH
SELECT N'Hội sở - PHÒNG QUẢN LÝ TÀI SẢN' AS BRANCH_NAME,
(N'Hôm nay, ngày ' + CONVERT(VARCHAR(5),DAY(GETDATE())) + N' tháng ' + CONVERT(VARCHAR(5),MONTH(GETDATE())) + N' năm ' + CONVERT(VARCHAR(5),YEAR(GETDATE())) + N' tại ') AS DD_MM_YY,
A.CONTENT AS TITLE
FROM ASS_USE_MULTI_MASTER A
WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--Table 1 Bên giao
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
FROM  ASS_USE_MULTI_MASTER A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--Table 2 Bên nhận
--Fix tạm UAT
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
    FROM  ASS_USE_CONFIRM_MASTER A
    LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
    LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
    WHERE A.USER_MASTER_ID = @USER_MASTER_ID))
BEGIN
SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
FROM  ASS_USE_CONFIRM_MASTER A
LEFT JOIN TL_USER B ON A.MAKER_ID = B.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
LEFT JOIN CM_BRANCH D ON A.BRANCH_ID = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEP_ID = E.DEP_ID
WHERE A.USER_MASTER_ID = @USER_MASTER_ID
END
ELSE
BEGIN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
END

--TABLE 3 DANH SÁCH TÀI SẢN
SELECT CASE 
       	WHEN B.ASSET_CODE IS NOT NULL AND B.ASSET_CODE <> '' THEN B.ASSET_CODE
       	ELSE B.ASS_CODE_TMP
       END AS ASSET_CODE, B.ASSET_NAME, D.UNIT_NAME, 1 AS QUANTITY, 
       E.STATUS_NAME, B.ASSET_SERIAL_NO AS SERIAL, R.REQ_CODE, B.NOTES AS QUY_CACH, A.NOTES, ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT
       ,CE.EMP_NAME
FROM ASS_USE_MULTI_DT A
LEFT JOIN TR_REQUEST_SHOP_DOC R ON A.REQ_ID = R.REQ_ID
LEFT JOIN ASS_MASTER B ON A.ASSET_ID = B.ASSET_ID
LEFT JOIN ASS_GROUP C ON B.GROUP_ID = C.GROUP_ID
LEFT JOIN CM_UNIT D ON C.UNIT = D.UNIT_ID 
LEFT JOIN ASS_STATUS E ON B.ASS_STATUS = E.STATUS_ID
LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID = CE.EMP_ID
WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--TABLE 4 CHỮ KÝ
SELECT TOP 1 E.TLFullName AS CHECKER_NAME, --BÊN NHẬN
ISNULL(C.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'CONFIRM'
                                                                  AND PP.CHECKER_ID = B.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2,--BÊN NHẬN
D.TLFullName AS MAKER_NAME, --BÊN GIAO 
--G.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
ISNULL(F.POS_NAME,'') + CHAR(13) + N'Đã ký ngày ' + FORMAT((SELECT top 1 PP.APPROVE_DT FROM PL_PROCESS PP 
                                                                  WHERE PP.REQ_ID = A.USER_MASTER_ID AND PP.PROCESS_ID = 'SEND'
                                                                  AND PP.CHECKER_ID = A.MAKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME --BÊN GIAO 
--H.POS_NAME AS POS_NAME_1  -- ĐƠN VỊ TRUNG GIAN
FROM ASS_USE_MULTI_MASTER A
LEFT JOIN ASS_USE_CONFIRM_MASTER B ON A.USER_MASTER_ID = B.USER_MASTER_ID
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN NHẬN
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN NHẬN
LEFT JOIN TL_USER D ON A.MAKER_ID = D.TLNANME --BÊN GIAO 
LEFT JOIN CM_EMPLOYEE_LOG F ON D.TLNANME = F.USER_DOMAIN
--LEFT JOIN TL_USER G ON A.CHECKER_ID = G.TLNANME -- DVTG
--LEFT JOIN CM_EMPLOYEE_LOG H ON A.CHECKER_ID = H.USER_DOMAIN --DVTG
WHERE A.USER_MASTER_ID = @USER_MASTER_ID

--TABLE 5 SỐ
SELECT @USER_MASTER_ID AS NO

--TABLE 6 ĐƠN VỊ TRUNG GIAN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
--SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(D.BRANCH_NAME,'') + ISNULL(' - ' + E.DEP_NAME,'') AS BRANCH_NAME
--FROM  ASS_USE_MULTI_MASTER A
--LEFT JOIN TL_USER B ON A.CHECKER_ID = B.TLNANME
--LEFT JOIN CM_EMPLOYEE_LOG C ON B.TLNANME = C.USER_DOMAIN
--LEFT JOIN CM_BRANCH D ON A.BRANCH_CREATE = D.BRANCH_ID
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
--WHERE A.USER_MASTER_ID = @USER_MASTER_ID

END