CREATE   OR ALTER  PROC [dbo].[rpt_BUD_UTILIZED_EFF_Excel]
@p_QUARTER INT,
@p_YEAR INT,
@p_FROM_DT VARCHAR(20),
@p_TO_DT VARCHAR(20)
AS
BEGIN
	DECLARE @p_FROM VARCHAR(20), @p_TO VARCHAR(20)
	IF(@p_QUARTER = 1)
	BEGIN
		SET @p_FROM = '01/01/'
		SET @p_TO = '31/03/'
	END
	ELSE IF(@p_QUARTER = 2)
	BEGIN
		SET @p_FROM = '01/04/'
		SET @p_TO = '30/06/'
	END
	ELSE IF(@p_QUARTER = 3)
	BEGIN
		SET @p_FROM = '01/07/'
		SET @p_TO = '30/09/'
	END
	ELSE IF(@p_QUARTER = 4)
	BEGIN
		SET @p_FROM = '01/10/'
		SET @p_TO = '30/12/'
	END
    IF(@p_FROM_DT IS NULL OR @p_FROM_DT = '' OR LEN(@p_FROM_DT) < 6)	SET @p_FROM_DT = @p_FROM + CONVERT(VARCHAR, @p_YEAR)
	IF(@p_TO_DT IS NULL OR @p_TO_DT = '' OR LEN(@p_TO_DT) < 6) SET @p_TO_DT = @p_TO + CONVERT(VARCHAR, @p_YEAR)

    DECLARE @RANGE_TIME INT = (SELECT DATEDIFF(DAY, CONVERT(DATE,@p_FROM_DT,103), CONVERT(DATE,@p_TO_DT,103)))

	DECLARE @TITLE NVARCHAR(500) = N'VIETBANK - TỔNG HỢP DANH SÁCH DOANH THU KHÁCH THUÊ QUÝ ' + CAST(@p_QUARTER AS VARCHAR(1)) + N' NĂM ' + CAST(@p_YEAR AS VARCHAR(4))

    DECLARE @Lst_CUST TABLE(CUST_CODE VARCHAR(50), CUST_NAME NVARCHAR(150))
    INSERT INTO @Lst_CUST SELECT CC.CUSTOMER_CODE, CC.CUSTOMER_NAME FROM CM_CUSTOMER CC WHERE CC.AUTH_STATUS = 'A'
    
    DECLARE @MoneyByMonth TABLE ( MONTH_DT NVARCHAR(150), AMT DECIMAL(18,2))


---- TABLE 0
    SELECT
        CAST(ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS VARCHAR(5)) STT, BCR.CUST_CODE AS MAKH,
        CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
             WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
             ELSE N'Miền Nam' END AS MAVM,
        ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TENKH,
        --- SELECT 12 Tháng
        SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
        SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
        SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
        SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
        SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
        SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
        SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
        SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
        SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
        SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
        SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
        SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12],
        0 AS [T13]
    FROM
        BUD_CONTRACT_RENT BCR
    LEFT JOIN
        BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID 
        AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
    LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
    WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
    GROUP BY
        BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
        BCR.CUST_NAME,CC.CUSTOMER_NAME
    
---- TABLE 1
    SELECT '' AS STT, '' AS MAKH, '' AS MAVM, N'TỔNG CỘNG' AS CUST_NAME,
           SUM(T1) AS SUM_T1,
           SUM(T2) AS SUM_T2,
           SUM(T3) AS SUM_T3,
           SUM(T4) AS SUM_T4,
           SUM(T5) AS SUM_T5,
           SUM(T6) AS SUM_T6,
           SUM(T7) AS SUM_T7,
           SUM(T8) AS SUM_T8,
           SUM(T9) AS SUM_T9,
           SUM(T10) AS SUM_T10,
           SUM(T11) AS SUM_T11,
           SUM(T12) AS SUM_T12,
           SUM(T1) + SUM(T2) + SUM(T3) + SUM(T4) + SUM(T5) + SUM(T6) + SUM(T7) + SUM(T8) + SUM(T9) + SUM(T10) + SUM(T11) + SUM(T12) AS SUM_T13
    FROM (
        SELECT
            ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS STT, BCR.CUST_CODE AS MAKH,
            CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
                 WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
                 ELSE N'Miền Nam' END AS MAVM,
            ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TEN_KH,
            --- SELECT 12 Tháng
            --- SELECT 12 Tháng
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12]
        FROM
            BUD_CONTRACT_RENT BCR
        LEFT JOIN
            BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID 
            AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
        LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
        WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
        GROUP BY
            BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
            BCR.CUST_NAME,CC.CUSTOMER_NAME
    ) AS SUM_NAM


---- TABLE 2    -- SUM TỔNG THEO NĂM
    SELECT STT, T1+T2+T3+T4+T5+T6+T7+T8+T9+T10+T11+T12 AS TONG_NAM
    FROM (
        SELECT
            ROW_NUMBER() OVER(ORDER BY BCR.CUST_CODE) AS STT, BCR.CUST_CODE AS MAKH,
            CASE WHEN LEFT(BCR.CUST_CODE,2) = 'MB' THEN N'Miền Bắc'
                 WHEN LEFT(BCR.CUST_CODE,2) = 'MT' THEN N'Miền Trung'
                 ELSE N'Miền Nam' END AS MAVM,
            ISNULL(BCR.CUST_NAME,ISNULL(CC.CUSTOMER_NAME, BCR.BUDC_RENT_NAME)) AS TEN_KH,
            --- SELECT 12 Tháng
            --- SELECT 12 Tháng
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T1],
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T2],
            SUM(CASE WHEN @p_QUARTER = 1 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T3],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T4],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T5],
            SUM(CASE WHEN @p_QUARTER = 2 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T6],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T7],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T8],
            SUM(CASE WHEN @p_QUARTER = 3 THEN  ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T9],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T10],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T11],
            SUM(CASE WHEN  @p_QUARTER = 4 THEN ISNULL(BCRM.PRICE_CONTRACT-BCR.PRICE_VAT,0) ELSE 0 END) AS [T12]
        FROM
            BUD_CONTRACT_RENT BCR
        LEFT JOIN
            BUD_CONTRACT_RENT_MOVEMENT BCRM ON BCR.BUDC_RENT_ID = BCRM.BUDC_RENT_ID 
            AND CONVERT(DATE,BCRM.START_DT,103) <= CONVERT(DATE,@p_TO_DT,103) AND CONVERT(DATE,BCRM.END_DT,103) >= CONVERT(DATE,@p_FROM_DT,103)
        LEFT JOIN CM_CUSTOMER CC ON CC.CUSTOMER_CODE = BCR.CUST_CODE
        WHERE BCR.BUDC_RENT_TYPE = 'CT' AND BCR.AUTH_STATUS = 'A'
        GROUP BY
            BCR.BUDC_RENT_NAME, BCR.CUST_CODE,
            BCR.CUST_NAME,CC.CUSTOMER_NAME
    ) AS TONG_NAM

---- TABLE 3
    SELECT @TITLE AS TITLE

END