AMS - VB

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