ALTER PROC dbo.rpt_ASS_USE_MULTI_CONFIRM_Excel
@USER_MASTER_ID VARCHAR(15) = null,
@BRANCH_ID VARCHAR(15) = null,
@FROMDATE VARCHAR(15) = null,
@TODATE VARCHAR(15) = null,
@IS_CONFIRM VARCHAR(1) = null
as
BEGIN
--	SELECT 
--		B.ASSET_CODE, B.ASSET_NAME, B.AMORT_MONTH, B.AMORTIZED_MONTH, B.AMORT_AMT, B.AMORTIZED_AMT, AMORT_AMT - B.AMORTIZED_AMT AS GT_CL, B.NOTES,
--		A.USER_MASTER_ID,CB.BRANCH_CODE,CB.BRANCH_NAME,1 AS SL,B.BUY_PRICE,'' AS UNIT,B.ASSET_SERIAL_NO AS SERIAL_NUMBER,
--		dbo.FN_GET_BRANCH_LEADER(C.BRANCH_ID, c.DEPT_ID) AS TLFullName,D.DEP_CODE,D.DEP_NAME,E.REQ_CODE
--	FROM ASS_USE_MULTI_MASTER A
--		INNER JOIN ASS_USE_MULTI_DT C ON C.USER_MASTER_ID = A.USER_MASTER_ID
--		INNER JOIN ASS_MASTER B ON B.ASSET_ID = C.ASSET_ID
--		LEFT JOIN CM_BRANCH CB ON CB.BRANCH_ID = C.BRANCH_ID
--    LEFT JOIN CM_DEPARTMENT D ON A.DEP_ID = D.DEP_ID
--    LEFT JOIN TR_REQUEST_SHOP_DOC E ON C.REQ_ID = E.REQ_ID
--		WHERE (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')
--		AND A.AUTH_STATUS = 'A' 
--		AND (C.BRANCH_ID = @BRANCH_ID OR @BRANCH_ID IS NULL OR @BRANCH_ID = '')
--		AND ((@IS_CONFIRM = 'Y' AND A.USER_MASTER_ID IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)) 
--		OR (@IS_CONFIRM = 'N' AND A.USER_MASTER_ID NOT IN (SELECT USER_MASTER_ID FROM ASS_CONFIRM_USE_MASTER)
--		OR (@IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)))
--		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')
--		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS STT
          ,RE.SO_PYC
          ,RE.NGAY_TAO_PYC
          ,RE.MA_PHIEU_XUAT
          ,RE.NOI_DUNG_BIEN_BAN
          ,RE.MA_DV_NHAN
          ,RE.TEN_DV_NHAN
          ,RE.MA_PB_NHAN
          ,RE.TEN_PB_NHAN
          ,RE.NGAY_XUAT
          ,RE.NGUOI_XUAT
          ,RE.NGUOI_DUYET
          ,RE.NGUOI_XAC_NHAN
    FROM (

        SELECT 
        A.REQ_CODE AS SO_PYC
        ,FORMAT(A.REQ_DT,'dd/MM/yyyy') AS NGAY_TAO_PYC
        ,B.USER_MASTER_ID AS MA_PHIEU_XUAT
        ,D.CONTENT AS NOI_DUNG_BIEN_BAN
        ,H.BRANCH_CODE AS MA_DV_NHAN
        ,H.BRANCH_NAME AS TEN_DV_NHAN
        ,I.DEP_CODE AS MA_PB_NHAN
        ,I.DEP_NAME AS TEN_PB_NHAN
        ,FORMAT(D.CREATE_DT,'dd/MM/yyyy') AS NGAY_XUAT
        ,E.TLFullName AS NGUOI_XUAT
        ,F.TLFullName AS NGUOI_DUYET
        ,G.TLFullName AS NGUOI_XAC_NHAN
        FROM TR_REQUEST_SHOP_DOC A
        LEFT JOIN ASS_USE_MULTI_DT B ON A.REQ_ID = B.REQ_ID
        LEFT JOIN ASS_USE_MULTI_MASTER D ON B.USER_MASTER_ID = D.USER_MASTER_ID
        LEFT JOIN ASS_USE_CONFIRM_MASTER C ON B.USER_MASTER_ID = C.USER_MASTER_ID
        LEFT JOIN TL_USER E ON D.MAKER_ID = E.TLNANME
        LEFT JOIN TL_USER F ON D.CHECKER_ID = F.TLNANME
        LEFT JOIN TL_USER G ON C.MAKER_ID = G.TLNANME
        LEFT JOIN CM_BRANCH H ON D.BRANCH_ID = H.BRANCH_ID
        LEFT JOIN CM_DEPARTMENT I ON D.DEP_ID = I.DEP_ID
        WHERE ((A.STATUS = 'APPROVE' AND @IS_CONFIRM = 'N')
		OR (A.STATUS = 'DONE' AND @IS_CONFIRM = 'Y')
		OR ((@IS_CONFIRM IS NULL OR @IS_CONFIRM = '') AND A.STATUS IN ('APPROVE','DONE')))
        AND A.REQ_TYPE = 'CPTS'
        AND (D.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')
        AND ((@IS_CONFIRM = 'Y' AND C.USER_CONFIRM_MASTER_ID IS NOT NULL AND C.USER_CONFIRM_MASTER_ID <> '') 
        		OR (@IS_CONFIRM = 'N' AND C.USER_CONFIRM_MASTER_ID IS NULL)
        		OR @IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)
    		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')
    		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')
    	GROUP BY A.REQ_CODE,A.REQ_DT,B.USER_MASTER_ID,D.CONTENT,H.BRANCH_CODE,H.BRANCH_NAME,I.DEP_CODE,I.DEP_NAME,D.CREATE_DT,E.TLFullName,F.TLFullName,G.TLFullName
        UNION ALL    
    
        SELECT 
          NULL AS SO_PYC
          ,NULL AS NGAY_TAO_PYC
          ,A.USER_MASTER_ID AS MA_PHIEU_XUAT
          ,A.CONTENT AS NOI_DUNG_BIEN_BAN
          ,H.BRANCH_CODE AS MA_DV_NHAN
          ,H.BRANCH_NAME AS TEN_DV_NHAN
          ,I.DEP_CODE AS MA_PB_NHAN
          ,I.DEP_NAME AS TEN_PB_NHAN
          ,FORMAT(A.CREATE_DT,'dd/MM/yyyy') AS NGAY_XUAT
          ,E.TLFullName AS NGUOI_XUAT
          ,F.TLFullName AS NGUOI_DUYET
          ,G.TLFullName AS NGUOI_XAC_NHAN
        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 A.MAKER_ID = E.TLNANME 
        LEFT JOIN TL_USER F ON A.CHECKER_ID = F.TLNANME
        LEFT JOIN TL_USER G ON B.MAKER_ID = G.TLNANME
        LEFT JOIN CM_BRANCH H ON A.BRANCH_ID = H.BRANCH_ID
        LEFT JOIN CM_DEPARTMENT I ON A.DEP_ID = I.DEP_ID
        WHERE NOT EXISTS(SELECT 1 FROM ASS_USE_MULTI_DT K 
                      WHERE K.USER_MASTER_ID = A.USER_MASTER_ID AND K.REQ_ID IS NOT NULL AND K.REQ_ID <> '')
        AND (A.USER_MASTER_ID = @USER_MASTER_ID OR @USER_MASTER_ID IS NULL OR @USER_MASTER_ID = '')
        AND ((@IS_CONFIRM = 'Y' AND B.USER_CONFIRM_MASTER_ID IS NOT NULL AND B.USER_CONFIRM_MASTER_ID <> '') 
        		OR (@IS_CONFIRM = 'N' AND B.USER_CONFIRM_MASTER_ID IS NULL)
        		OR @IS_CONFIRM= '' OR @IS_CONFIRM IS NULL)
    		AND	(CONVERT(DATE,A.APPROVE_DT) >= CONVERT(DATE, @FROMDATE, 103) OR @FROMDATE is NULL OR @FROMDATE = '')
    		AND	(CONVERT(DATE,A.APPROVE_DT) <= CONVERT(DATE, @TODATE, 103) OR @TODATE is NULL OR @TODATE = '')
   ) RE
   ORDER BY RE.SO_PYC
END