ALTER PROC dbo.ASS_COL_MULTI_HANDOVER_RECORD
@COL_MULTI_MASTER_ID VARCHAR(15) = NULL
AS
BEGIN
--Table 0 Nội dung && BRANCH
SELECT TOP 1 B.BRANCH_NAME  + ISNULL(' - ' + C.DEP_NAME,'') AS BRANCH_NAME,
D.CONTENT AS TITLE
,(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
FROM ASS_COLLECT_MULTI_DT A
LEFT JOIN CM_BRANCH B ON A.BRANCH_ID = B.BRANCH_ID
LEFT JOIN CM_DEPARTMENT C ON A.DEPT_ID_USE = C.DEP_ID
LEFT JOIN ASS_COLLECT_MULTI_MASTER D ON A.COL_MULTI_MASTER_ID = D.COL_MULTI_MASTER_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--Table 1 Bên giao
--Fix tạm UAT
IF(EXISTS(SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME
          FROM  ASS_COLLECT_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.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID))
BEGIN
--    IF(EXISTS(SELECT 1 FROM ASS_COLLECT_MULTI_MASTER acmm WHERE acmm.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID AND acmm.REQ_ID IS NOT NULL AND acmm.REQ_ID <> ''))
--    BEGIN
--        SELECT TOP 1  C.TLFullName, C.ADDRESS, C.PHONE, D.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
--        FROM ASS_COLLECT_MULTI_MASTER A
--        LEFT JOIN TR_REQUEST_SHOP_DOC B ON A.REQ_ID = B.REQ_ID
--        LEFT JOIN TL_USER C ON B.MAKER_ID = C.TLNANME
--        LEFT JOIN CM_EMPLOYEE_LOG D ON B.MAKER_ID = D.USER_DOMAIN
--        LEFT JOIN CM_BRANCH E ON C.TLSUBBRID = E.BRANCH_ID
--        LEFT JOIN CM_DEPARTMENT G ON C.DEP_ID = G.DEP_ID
--        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--    END
--    ELSE
--    BEGIN
        SELECT TOP 1 B.TLFullName, B.ADDRESS, B.PHONE, C.POS_NAME, ISNULL(E.BRANCH_NAME,'') + ISNULL(' - ' + G.DEP_NAME,'') AS BRANCH_NAME
        FROM  ASS_COLLECT_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 E ON B.TLSUBBRID = E.BRANCH_ID
        LEFT JOIN CM_DEPARTMENT G ON B.DEP_ID = G.DEP_ID
        WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID
--    END

END
ELSE
BEGIN
SELECT '' TLFullName, '' ADDRESS, '' PHONE, '' POS_NAME
END


--Table 2 Bên nhận
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_COLLECT_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_ID = D.BRANCH_ID
LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID



--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, REQ.REQ_CODE AS REQ_CODE,B.NOTES AS QUY_CACH, A.NOTES,
       --ROW_NUMBER() OVER (PARTITION BY B.ASSET_NAME ORDER BY B.ASSET_NAME) AS STT,
       ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT,
       CA.CONTENT AS COLLECT_REASON,
       CE.EMP_NAME AS EMP_USE
FROM ASS_COLLECT_MULTI_DT A
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 ASS_COLLECT_MULTI_MASTER  F ON A.COL_MULTI_MASTER_ID = F.COL_MULTI_MASTER_ID
LEFT JOIN TR_REQUEST_SHOP_DOC REQ ON F.REQ_ID = REQ.REQ_ID
LEFT JOIN CM_ALLCODE CA ON A.PURPOSE_ID = CA.CDVAL AND CA.CDNAME = 'REASON_ASS_REQ' AND CA.CDTYPE = 'REQ_ASSET'
LEFT JOIN CM_EMPLOYEE CE ON A.EMP_ID_USE = CE.EMP_ID
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 4 CHỮ KÝ
--SELECT ''
SELECT TOP 1 E.TLFullName AS MAKER_NAME, --BÊN GIAO
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.COL_MULTI_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,--BÊN GIAO
D.TLFullName AS CHECKER_NAME, --BÊN NHẬN
--D.TLFullName AS INTERMEDIATE_UNIT_NAME, --ĐƠN VỊ TRUNG GIAN
--F.POS_NAME AS POS_NAME_1, --ĐƠ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.COL_MULTI_MASTER_ID AND PP.PROCESS_ID = 'APPROVE'
                                                                  AND PP.CHECKER_ID = A.CHECKER_ID
                                                                  ORDER BY PP.ID DESC),'dd/MM/yyyy HH:mm:ss') AS POS_NAME_2  --BÊN NHẬN 
FROM ASS_COLLECT_MULTI_MASTER A
LEFT JOIN ASS_COLLECT_CONFIRM_MASTER B ON A.COL_MULTI_MASTER_ID = B.COL_MULTI_MASTER_ID
LEFT JOIN TL_USER E ON B.MAKER_ID = E.TLNANME --BÊN GIAO
LEFT JOIN CM_EMPLOYEE_LOG C ON B.MAKER_ID = C.USER_DOMAIN --BÊN GIAO
LEFT JOIN TL_USER D ON A.CHECKER_ID = D.TLNANME --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
LEFT JOIN CM_EMPLOYEE_LOG F ON A.CHECKER_ID = F.USER_DOMAIN --BÊN NHẬN + ĐƠN VỊ TRUNG GIAN
WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

--TABLE 5 SỐ
SELECT @COL_MULTI_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_COLLECT_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_ID = D.BRANCH_ID
--LEFT JOIN CM_DEPARTMENT E ON A.DEPT_CREATE = E.DEP_ID
--WHERE A.COL_MULTI_MASTER_ID = @COL_MULTI_MASTER_ID

END