ALTER PROC dbo.rpt_PYC_CAPPHAT
@REQ_ID VARCHAR(15) = NULL

AS
BEGIN

 DECLARE 
@SHOW_TABLE_DVCM_KHAC VARCHAR(1) = NULL,@SHOW_TABLE_DVCM_IT VARCHAR(1) = NULL

--TABLE 0
SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT, AG.GROUP_NAME AS GR_NAME, AG.GROUP_CODE AS GR_CODE, 
cd.DEP_NAME, ce.EMP_NAME, trsdd.QTY_ETM, trsdd.REASON, TU.TLFullName AS MAKER, TU2.TLFullName AS TDV
FROM TR_REQUEST_SHOP_DOC_DT trsdd
LEFT JOIN TR_REQUEST_SHOP_DOC trsd ON trsdd.REQ_DOC_ID = trsd.REQ_ID
LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID
LEFT JOIN CM_EMPLOYEE ce ON trsdd.EMP_ID = ce.EMP_ID
LEFT JOIN ASS_GROUP AG ON trsdd.ASS_GROUP_ID = AG.GROUP_ID
LEFT JOIN TL_USER TU ON TU.TLNANME = trsd.MAKER_ID
LEFT JOIN TL_USER TU2 ON TU2.TLNANME = trsd.CHECKER_ID
WHERE trsdd.REQ_DOC_ID = @REQ_ID AND REQ_DT_TYPE = 'ORGINAL'
--GROUP BY AG.GROUP_NAME, AG.GROUP_CODE, 
--cd.DEP_NAME, ce.EMP_NAME, trsdd.QTY_ETM, trsdd.REASON, TU.TLFullName, TU2.TLNANME
ORDER BY STT
  
--TABLE 1 DVCM KHOI CNTT
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE = '05J00'))
BEGIN
 SELECT TOP 1 A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(PP.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    LEFT JOIN PL_PROCESS PP ON A.REQ_ID = PP.REQ_ID AND A.MAKER_ID = PP.CHECKER_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE = '05J00'
ORDER BY PP.APPROVE_DT DESC

    SET @SHOW_TABLE_DVCM_IT = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END


--TABLE 2 DVCM KHAC
IF(EXISTS(SELECT 1 
          FROM TR_REQUEST_SHOP_COSTCENTER A
          LEFT JOIN CM_DEPARTMENT B ON A.COST_ID = B.DEP_ID
          WHERE A.REQ_ID = @REQ_ID AND B.DEP_CODE <> '05J00'))
BEGIN
    SELECT A.ASS_STATUS, A.RE_CONTENT, A.NOTES, E.TLFullName AS SURVEY_STAFF, ISNULL(B.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + 
    FORMAT(
    (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = A.REQ_ID AND PP.CHECKER_ID = A.MAKER_ID ORDER BY PP.APPROVE_DT DESC)
    , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_COSTCENTER A
    LEFT JOIN CM_EMPLOYEE_LOG B ON A.MAKER_ID = B.USER_DOMAIN
    LEFT JOIN TL_USER E ON A.MAKER_ID = E.TLNANME
    LEFT JOIN CM_DEPARTMENT cd ON A.COST_ID = cd.DEP_ID
    WHERE A.REQ_ID = @REQ_ID AND cd.DEP_CODE <> '05J00'
    ORDER BY A.COST_ID

    SET @SHOW_TABLE_DVCM_KHAC = '1'
END
ELSE
BEGIN
    SELECT '' ASS_STATUS, '' RE_CONTENT,'' NOTES, ''SURVEY_STAFF, '' POS_NAME
END

--TABLE 3 DANH GIA VA DE XUAT CUA P.QLTS 
IF(EXISTS(SELECT 1 FROM TR_REQUEST_SHOP_DOC trsd
LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID
LEFT JOIN PL_REQUEST_PROCESS PRP ON trsd.REQ_ID = PRP.REQ_ID AND PRP.PROCESS_ID = 'QLTS_NL'
LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
WHERE trsd.REQ_ID = @REQ_ID))
BEGIN
    SELECT DISTINCT trsd.HO_NOTES, tu.TLFullName, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
    (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = trsd.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC)
    , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
    FROM TR_REQUEST_SHOP_DOC trsd
    LEFT JOIN CM_DEPARTMENT cd ON trsd.DEP_ID = cd.DEP_ID
    LEFT JOIN PL_REQUEST_PROCESS PRP ON trsd.REQ_ID = PRP.REQ_ID AND PRP.PROCESS_ID = 'QLTS_NL'
    LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
    LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
    WHERE trsd.REQ_ID = @REQ_ID
    END
ELSE BEGIN SELECT  N'' NOTES, N'' TLFullName, N'' POS_NAME END

--TABLE 4 PHE DUYET DVCM KHOI CNTT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'))
    BEGIN  
    	    SELECT TOP 1 ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(PP.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          LEFT JOIN PL_PROCESS PP ON A.REQ_ID = PP.REQ_ID
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE = '05J00'
          ORDER BY PP.APPROVE_DT DESC
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES        
    END
END
ELSE
BEGIN
SELECT N'' NOTES
END

--QTLS_D 5
IF(EXISTS(SELECT 1
    FROM PL_REQUEST_PROCESS PRP
    LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
    LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
    WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'QLTS_D'))
    BEGIN
        SELECT DISTINCT PRP.NOTES AS HO_NOTES, tu.TLFullName, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
        (SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC)
        , 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
        FROM PL_REQUEST_PROCESS PRP
        LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
        LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
        LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID
        WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'QLTS_D'
    END
ELSE BEGIN SELECT  N'' HO_NOTES, N'' TLFullName, N'' POS_NAME END

--TTQTLS_D 6
SELECT DISTINCT ISNULL(PRP.NOTES, '') AS HO_NOTES, ISNULL(tu.TLFullName, '') AS TLFullName , ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
(SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC)
, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
FROM PL_REQUEST_PROCESS PRP
LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
--LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID
WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'TTQLTS_D'

--TABLE 7
SELECT TU.TLFullName AS MAKER, TU2.TLFullName AS TDV, ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
A.CREATE_DT
, 'dd/MM/yyyy HH:mm:ss') AS POS_MAKER, 
ISNULL(CE2.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
A.APPROVE_DT
, 'dd/MM/yyyy HH:mm:ss') AS POS_TDV
FROM TR_REQUEST_SHOP_DOC A
LEFT JOIN TL_USER TU ON TU.TLNANME = A.MAKER_ID
LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = A.MAKER_ID
LEFT JOIN TL_USER TU2 ON TU2.TLNANME = A.CHECKER_ID
LEFT JOIN CM_EMPLOYEE_LOG CE2 ON CE2.USER_DOMAIN = A.CHECKER_ID
WHERE A.REQ_ID = @REQ_ID

--TABLE 8 SỐ PYC
SELECT trsd.REQ_CODE FROM TR_REQUEST_SHOP_DOC trsd WHERE trsd.REQ_ID = @REQ_ID

--PHUCVH TABLE 9 NGÀY TDV DUYỆT
SELECT TOP 1 N'Ngày ' +  CONVERT(VARCHAR(5),DAY(pp.APPROVE_DT)) + 
            N' tháng ' + CONVERT(VARCHAR(5),MONTH(pp.APPROVE_DT)) + 
            N' năm ' + CONVERT(VARCHAR(10),YEAR(pp.APPROVE_DT)) AS APPROVE_DT
FROM PL_PROCESS pp WHERE pp.REQ_ID = @REQ_ID AND pp.PROCESS_ID = 'APPNEW' ORDER BY pp.ID DESC

--TABLE 10 PHÊ DUYỆT DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN  
    IF (EXISTS(SELECT 1 FROM PL_REQUEST_PROCESS A
            LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
            WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'))
    BEGIN  
    	    SELECT ISNULL(A.NOTES,'') AS NOTES, E.TLFullName, ISNULL(D.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(A.APPROVE_DT, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
          FROM PL_REQUEST_PROCESS A
          LEFT JOIN CM_DEPARTMENT C ON A.DEP_ID = C.DEP_ID
          LEFT JOIN CM_EMPLOYEE_LOG D ON A.CHECKER_ID = D.USER_DOMAIN
          LEFT JOIN TL_USER E ON D.USER_DOMAIN = E.TLNANME
          WHERE A.REQ_ID = @REQ_ID AND A.PROCESS_ID = 'DVCM_D' AND C.DEP_CODE <> '05J00'
          ORDER BY C.DEP_ID
    END
    ELSE
    BEGIN
        SELECT '' AS NOTES
        FROM TR_REQUEST_SHOP_COSTCENTER A
        LEFT JOIN CM_DEPARTMENT C ON A.COST_ID = C.DEP_ID
        WHERE A.REQ_ID = @REQ_ID AND C.DEP_CODE <> '05J00'
    END   
END
ELSE
BEGIN
    SELECT N'' NOTES
END

--TABLE 11 PHÊ DUYỆT TBP_QLTS
SELECT DISTINCT ISNULL(PRP.NOTES, '') AS HO_NOTES, ISNULL(tu.TLFullName, '') AS TLFullName , ISNULL(CE.POS_NAME, '') + CHAR(13) + N'Đã ký ngày ' + FORMAT(
(SELECT TOP 1 PP.APPROVE_DT FROM PL_PROCESS PP WHERE PP.REQ_ID = PRP.REQ_ID AND PP.CHECKER_ID = PRP.CHECKER_ID ORDER BY PP.APPROVE_DT DESC)
, 'dd/MM/yyyy HH:mm:ss') AS POS_NAME
FROM PL_REQUEST_PROCESS PRP
LEFT JOIN TL_USER tu ON PRP.CHECKER_ID = tu.TLNANME
LEFT JOIN CM_EMPLOYEE_LOG CE ON CE.USER_DOMAIN = PRP.CHECKER_ID
--LEFT JOIN PL_PROCESS PP ON PRP.REQ_ID = PP.REQ_ID AND PRP.CHECKER_ID = PP.CHECKER_ID
WHERE PRP.REQ_ID = @REQ_ID AND PRP.PROCESS_ID = 'TBP_D'


--SHOW TABLE DVCM IT
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL)
BEGIN
    SELECT N'DVCMIT' AS MERGE_REGION
END

--SHOW TABLE DVCM KHÁC
IF(@SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'DVCMKHAC' AS MERGE_REGION
END

--SHOW TITLE ĐÁNH GIÁ CỦA ĐƠN VỊ CHUYÊN MÔN
IF(@SHOW_TABLE_DVCM_IT IS NOT NULL OR @SHOW_TABLE_DVCM_KHAC IS NOT NULL)
BEGIN
    SELECT N'TITLEDVCM' AS MERGE_REGION
END


END