SELECT r.ID, CASE WHEN r.PROPERTY_TYPE = 'HOUSE' THEN h.ROOM
    ELSE p.PLACE_NAME END PLACE_NAME, r.PROPERTY_ID, r.PROPERTY_TYPE,
h.BUILD_AREA, r.FEE_ITEM_ID,
CONCAT(DATE_FORMAT(MIN(r.COST_PERIOD_BEGIN), '%Y/%m/%d'),
    '-', DATE_FORMAT(MAX(r.COST_PERIOD_END), '%Y/%m/%d')) COST_PERIOD,
r.PRICE, SUM(r.RECEIVABLE_CAPITAL) RECEIVABLE_CAPITAL,
SUM(r.RECEIVABLE_LATE_FEE) RECEIVABLE_LATE_FEE,
SUM(IFNULL(dr.DEDUCTED_CAPITAL, 0) + IFNULL(dr.DEDUCTED_LATE_FEE, 0)) DEDUCTED_TOTAL,
SUM(r.RECEIVED_CAPITAL) RECEIVED_CAPITAL, SUM(r.RECEIVED_LATE_FEE) RECEIVED_LATE_FEE,
SUM(CASE WHEN r.COST_STATUS IN (1, 2) THEN 1 ELSE 0 END) MONTHLY_FEE_ARREARS,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) >= 0
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 12
        THEN 1 ELSE 0 END) LESS_THEN_ONE_YEAR_COUNT,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) >= 0
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 12
        THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
        ELSE 0 END) LESS_THEN_ONE_YEAR_RECEIVABLE_TOTAL,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 12
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 24
        THEN 1 ELSE 0 END) ONE_TWO_YEAR_COUNT,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 12
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 24
        THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
        ELSE 0 END) ONE_TWO_RECEIVABLE_TOTAL,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 24
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 36
        THEN 1 ELSE 0 END) TWO_THREE_YEAR_COUNT,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 24
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 36
        THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
        ELSE 0 END) TWO_THREE_YEAR_RECEIVABLE_TOTAL,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 36
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 48
        THEN 1 ELSE 0 END) THREE_FOUR_YEAR_COUNT,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 36
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 48
        THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
        ELSE 0 END) THREE_FOUR_YEAR_RECEIVABLE_TOTAL,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 48
        THEN 1 ELSE 0 END) OVER_FOUR_YEAR_COUNT,
SUM(CASE WHEN r.COST_STATUS IN (1, 2)
        AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 48
        THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
        ELSE 0 END) OVER_FOUR_YEAR_RECEIVABLE_TOTAL
FROM CHARGE_FINANCE_RECEIVABLE_ACCOUNTS_DETAIL r
LEFT JOIN BASE_COMMUNITY c
ON r.COMMUNITY_ID = c.ID
LEFT JOIN BASE_BUILDING b
ON r.BUILD_ID = b.ID
LEFT JOIN (
    SELECT CONCAT(u.UNIT_NAME, '-', h.ROOM_NO) ROOM, h.ID, h.BUILD_AREA
    FROM BASE_HOUSE h
    LEFT JOIN BASE_UNIT u
    ON h.UNIT_ID = u.ID
) h
ON r.PROPERTY_ID = h.ID
LEFT JOIN (
    SELECT p.ID, CONCAT(a.AREA_NAME, '-', p.PLACE_NAME) PLACE_NAME
    FROM PARK_PLACE p, PARK_AREA a
    WHERE p.AREA_ID = a.ID
) p
ON r.PROPERTY_ID = p.ID
LEFT JOIN CHARGE_FINANCE_DEDUCTED_RECORD_DETAIL dr
ON dr.RECEIVABLE_DETAIL_ID = r.ID
WHERE r.STATUS = 1
GROUP BY r.PROPERTY_ID, r.FEE_ITEM_ID, r.PRICE
UNION ALL
SELECT 0 ID, h.PLACE_NAME, h.PROPERTY_ID, h.PROPERTY_TYPE,
h.BUILD_AREA, 0 FEE_ITEM_ID, NULL COST_PERIOD, NULL PRICE,
0 RECEIVABLE_CAPITAL, 0 RECEIVABLE_LATE_FEE, 0 DEDUCTED_TOTAL,
0 RECEIVED_CAPITAL, 0 RECEIVED_LATE_FEE, 0 MONTHLY_FEE_ARREARS,
0 LESS_THEN_ONE_YEAR_COUNT, 0 LESS_THEN_ONE_YEAR_RECEIVABLE_TOTAL,
0 ONE_TWO_YEAR_COUNT, 0 ONE_TWO_RECEIVABLE_TOTAL,
0 TWO_THREE_YEAR_COUNT, 0 TWO_THREE_YEAR_RECEIVABLE_TOTAL,
0 THREE_FOUR_YEAR_COUNT, 0 THREE_FOUR_YEAR_RECEIVABLE_TOTAL,
0 OVER_FOUR_YEAR_COUNT, 0 OVER_FOUR_YEAR_RECEIVABLE_TOTAL
FROM (
    SELECT CONCAT(u.UNIT_NAME, '-', h.ROOM_NO) PLACE_NAME,
    h.ID PROPERTY_ID, 'HOUSE' PROPERTY_TYPE, h.BUILD_AREA
    FROM BASE_HOUSE h
    LEFT JOIN BASE_UNIT u
    ON h.UNIT_ID = u.ID
    WHERE h.STATUS = 1
    UNION ALL
    SELECT CONCAT(a.AREA_NAME, '-', p.PLACE_NAME) PLACE_NAME,
    p.ID PROPERTY_ID, 'PARK_PLACE' PROPERTY_TYPE, 0
    FROM PARK_PLACE p
    LEFT JOIN PARK_AREA a
    ON p.AREA_ID = a.ID
    WHERE p.STATUS = 1
) h
WHERE NOT EXISTS (
    SELECT 1 FROM CHARGE_FINANCE_RECEIVABLE_ACCOUNTS_DETAIL r
    WHERE r.STATUS = 1
    AND h.PROPERTY_ID = r.PROPERTY_ID
)