SHARE
TWEET

Untitled

a guest Oct 21st, 2019 111 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT r.ID, CASE WHEN r.PROPERTY_TYPE = 'HOUSE' THEN h.ROOM
  2.     ELSE p.PLACE_NAME END PLACE_NAME, r.PROPERTY_ID, r.PROPERTY_TYPE,
  3. h.BUILD_AREA, r.FEE_ITEM_ID,
  4. CONCAT(DATE_FORMAT(MIN(r.COST_PERIOD_BEGIN), '%Y/%m/%d'),
  5.     '-', DATE_FORMAT(MAX(r.COST_PERIOD_END), '%Y/%m/%d')) COST_PERIOD,
  6. r.PRICE, SUM(r.RECEIVABLE_CAPITAL) RECEIVABLE_CAPITAL,
  7. SUM(r.RECEIVABLE_LATE_FEE) RECEIVABLE_LATE_FEE,
  8. SUM(IFNULL(dr.DEDUCTED_CAPITAL, 0) + IFNULL(dr.DEDUCTED_LATE_FEE, 0)) DEDUCTED_TOTAL,
  9. SUM(r.RECEIVED_CAPITAL) RECEIVED_CAPITAL, SUM(r.RECEIVED_LATE_FEE) RECEIVED_LATE_FEE,
  10. SUM(CASE WHEN r.COST_STATUS IN (1, 2) THEN 1 ELSE 0 END) MONTHLY_FEE_ARREARS,
  11. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  12.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) >= 0
  13.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 12
  14.         THEN 1 ELSE 0 END) LESS_THEN_ONE_YEAR_COUNT,
  15. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  16.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) >= 0
  17.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 12
  18.         THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
  19.         ELSE 0 END) LESS_THEN_ONE_YEAR_RECEIVABLE_TOTAL,
  20. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  21.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 12
  22.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 24
  23.         THEN 1 ELSE 0 END) ONE_TWO_YEAR_COUNT,
  24. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  25.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 12
  26.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 24
  27.         THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
  28.         ELSE 0 END) ONE_TWO_RECEIVABLE_TOTAL,
  29. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  30.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 24
  31.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 36
  32.         THEN 1 ELSE 0 END) TWO_THREE_YEAR_COUNT,
  33. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  34.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 24
  35.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 36
  36.         THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
  37.         ELSE 0 END) TWO_THREE_YEAR_RECEIVABLE_TOTAL,
  38. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  39.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 36
  40.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 48
  41.         THEN 1 ELSE 0 END) THREE_FOUR_YEAR_COUNT,
  42. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  43.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 36
  44.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) <= 48
  45.         THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
  46.         ELSE 0 END) THREE_FOUR_YEAR_RECEIVABLE_TOTAL,
  47. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  48.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 48
  49.         THEN 1 ELSE 0 END) OVER_FOUR_YEAR_COUNT,
  50. SUM(CASE WHEN r.COST_STATUS IN (1, 2)
  51.         AND TIMESTAMPDIFF(MONTH, IFNULL(r.PAYMENT_DURATION, NOW()), NOW()) > 48
  52.         THEN r.RECEIVABLE_CAPITAL + r.RECEIVABLE_LATE_FEE
  53.         ELSE 0 END) OVER_FOUR_YEAR_RECEIVABLE_TOTAL
  54. FROM CHARGE_FINANCE_RECEIVABLE_ACCOUNTS_DETAIL r
  55. LEFT JOIN BASE_COMMUNITY c
  56. ON r.COMMUNITY_ID = c.ID
  57. LEFT JOIN BASE_BUILDING b
  58. ON r.BUILD_ID = b.ID
  59. LEFT JOIN (
  60.     SELECT CONCAT(u.UNIT_NAME, '-', h.ROOM_NO) ROOM, h.ID, h.BUILD_AREA
  61.     FROM BASE_HOUSE h
  62.     LEFT JOIN BASE_UNIT u
  63.     ON h.UNIT_ID = u.ID
  64. ) h
  65. ON r.PROPERTY_ID = h.ID
  66. LEFT JOIN (
  67.     SELECT p.ID, CONCAT(a.AREA_NAME, '-', p.PLACE_NAME) PLACE_NAME
  68.     FROM PARK_PLACE p, PARK_AREA a
  69.     WHERE p.AREA_ID = a.ID
  70. ) p
  71. ON r.PROPERTY_ID = p.ID
  72. LEFT JOIN CHARGE_FINANCE_DEDUCTED_RECORD_DETAIL dr
  73. ON dr.RECEIVABLE_DETAIL_ID = r.ID
  74. WHERE r.STATUS = 1
  75. GROUP BY r.PROPERTY_ID, r.FEE_ITEM_ID, r.PRICE
  76. UNION ALL
  77. SELECT 0 ID, h.PLACE_NAME, h.PROPERTY_ID, h.PROPERTY_TYPE,
  78. h.BUILD_AREA, 0 FEE_ITEM_ID, NULL COST_PERIOD, NULL PRICE,
  79. 0 RECEIVABLE_CAPITAL, 0 RECEIVABLE_LATE_FEE, 0 DEDUCTED_TOTAL,
  80. 0 RECEIVED_CAPITAL, 0 RECEIVED_LATE_FEE, 0 MONTHLY_FEE_ARREARS,
  81. 0 LESS_THEN_ONE_YEAR_COUNT, 0 LESS_THEN_ONE_YEAR_RECEIVABLE_TOTAL,
  82. 0 ONE_TWO_YEAR_COUNT, 0 ONE_TWO_RECEIVABLE_TOTAL,
  83. 0 TWO_THREE_YEAR_COUNT, 0 TWO_THREE_YEAR_RECEIVABLE_TOTAL,
  84. 0 THREE_FOUR_YEAR_COUNT, 0 THREE_FOUR_YEAR_RECEIVABLE_TOTAL,
  85. 0 OVER_FOUR_YEAR_COUNT, 0 OVER_FOUR_YEAR_RECEIVABLE_TOTAL
  86. FROM (
  87.     SELECT CONCAT(u.UNIT_NAME, '-', h.ROOM_NO) PLACE_NAME,
  88.     h.ID PROPERTY_ID, 'HOUSE' PROPERTY_TYPE, h.BUILD_AREA
  89.     FROM BASE_HOUSE h
  90.     LEFT JOIN BASE_UNIT u
  91.     ON h.UNIT_ID = u.ID
  92.     WHERE h.STATUS = 1
  93.     UNION ALL
  94.     SELECT CONCAT(a.AREA_NAME, '-', p.PLACE_NAME) PLACE_NAME,
  95.     p.ID PROPERTY_ID, 'PARK_PLACE' PROPERTY_TYPE, 0
  96.     FROM PARK_PLACE p
  97.     LEFT JOIN PARK_AREA a
  98.     ON p.AREA_ID = a.ID
  99.     WHERE p.STATUS = 1
  100. ) h
  101. WHERE NOT EXISTS (
  102.     SELECT 1 FROM CHARGE_FINANCE_RECEIVABLE_ACCOUNTS_DETAIL r
  103.     WHERE r.STATUS = 1
  104.     AND h.PROPERTY_ID = r.PROPERTY_ID
  105. )
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top