Line 1: select
2: a.id_listing,
3: a.reg_no as listing_reg_no,
4: a.id_category,
5: if (
6: /* 'Apartment / Condo (Highrise)' */
7: a.id_category = 2010,
8: if (
9: a.id_objective = 2000,
10: concat(
11: ap42.itm_desc,
12: ', ',
13: if (
14: (
15: a.id_type = 2024 /* Penthouse */
16: or
17: a.id_type = 2021 /* Service Residence */
18: ),
19: concat (
20: ifnull( a.storey, '1' ),
21: '-storey, '
22: ),
23: ''
24: ),
25: a.holding,
26: ', ',
27: ap43.itm_desc,
28: ', ',
29: ifnull( a.position, '' )
30: ),
31: concat(
32: ap42.itm_desc,
33: ', ',
34: if (
35: (
36: a.id_type = 2024 /* Penthouse */
37: or
38: a.id_type = 2021 /* Service Residence */
39: ),
40: concat (
41: ifnull( a.storey, '1' ),
42: '-storey, '
43: ),
44: ''
45: ),
46: ifnull( a.position, '' )
47: )
48: ),
49: if (
50: /* 'House / Bungalow (Landed)' */
51: a.id_category = 2011,
52: if (
53: a.id_objective = 2000,
54: concat(
55: ap42.itm_desc,
56: ', ',
57: a.storey,
58: '-storey, ',
59: a.holding,
60: ', ',
61: ap43.itm_desc,
62: ', ',
63: ifnull( a.position, '' )
64: ),
65: concat(
66: ap42.itm_desc,
67: ', ',
68: a.storey,
69: '-storey, ',
70: ifnull( a.position, '' )
71: )
72: ),
73: if (
74: /* 'Commercial / Office / Industry / Building' */
75: a.id_category = 2012,
76: if (
77: a.id_objective = 2000,
78: concat(
79: ap42.itm_desc,
80: ', ',
81: if (
82: (
83: a.id_type = 2043 /* Building */
84: or
85: a.id_type = 2045 /* Factory */
86: or
87: a.id_type = 2042 /* Office Lot */
88: or
89: a.id_type = 2046 /* Shop Lot */
90: or
91: a.id_type = 2044 /* Warehouse */
92: ),
93: concat (
94: ifnull( a.storey, '1' ),
95: '-storey, '
96: ),
97: ''
98: ),
99: a.holding,
100: ', ',
101: ap43.itm_desc,
102: ', ',
103: ifnull( a.position, '' )
104: ),
105: concat(
106: ap42.itm_desc,
107: ', ',
108: a.storey,
109: '-storey, ',
110: ifnull( a.position, '' )
111: )
112: ),
113: if (
114: /* 'Land' */
115: a.id_category = 2013,
116: concat(
117: ap42.itm_desc,
118: ' ',
119: ap41.itm_desc,
120: ', ',
121: a.holding,
122: ', ',
123: ap43.itm_desc
124: ),
125: if (
126: /* 'Hotel / Resort / Homestay' */
127: a.id_category = 2014,
128: concat(
129: ap42.itm_desc,
130: ', ',
131: if (
132: a.id_type = 2049, /* Homestay */
133: concat (
134: ifnull( a.storey, '1' ),
135: '-storey, '
136: ),
137: if (
138: ifnull( a.floor, '0' ) > 0,
139: concat(
140: a.floor,
141: '-floor, '
142: ),
143: ''
144: )
145: ),
146: a.holding,
147: ', ',
148: ap43.itm_desc,
149: if (
150: a.id_type = 2049, /* Homestay */
151: concat(
152: ', ',
153: ifnull( a.position, '' )
154: ),
155: ''
156: )
157: ),
158: '-'
159: )
160: )
161: )
162: )
163: ) as category_type_title_holding_lottype_storey,
164: if (
165: a.id_objective = 2000,
166: 'FOR SALE',
167: if (
168: a.id_objective = 2001,
169: 'FOR RENTAL',
170: if (
171: a.id_objective = 2008,
172: 'NEW PROJECT',
173: ''
174: )
175: )
176: ) as modus,
177: if (
178: (
179: a.status = 'SOLD' or a.status = 'SOLD_BY_OTHERS'
180: or
181: a.status = 'BOOKED'
182: or
183: a.status = 'AVAILABLE' and a.publish = 'N'
184: or
185: a.status = 'AVAILABLE' and a.publish = 'EXPIRED'
186: or
187: a.status = 'AVAILABLE' and a.publish = 'UNPUBLISHED'
188: or
189: a.status = 'CANCEL'
190: )
191: ,
192: 'Y',
193: 'N'
194: ) as status_unavailable,
195: a.status,
196: ifnull( a.publish_dt, '' ) as publish_dt,
197: /* a.asking_price, a.asking_price_previous,
198: a.asking_rental, a.asking_rental_previous,*/
199: if (
200: (
201: a.id_objective = 2000 /* For Sale (WTS) */
202: or
203: a.id_objective = 2008 /* New Project */
204: ),
205: a.asking_price,
206: a.asking_rental
207: ) as price,
208: if (
209: (
210: a.id_objective = 2000 /* For Sale (WTS) */
211: or
212: a.id_objective = 2008 /* New Project */
213: ),
214: a.asking_price_previous,
215: a.asking_rental_previous
216: ) as price_previous,
217: a.diff_market_value_vs_asking_price,
218: if (
219: trim( ifnull( a.diff_market_value_vs_asking_price, '' ) ) <> '',
220: if (
221: a.diff_market_value_vs_asking_price < 0,
222: 'Y',
223: 'N'
224: ),
225: 'N'
226: ) as below_market,
227: a.room, a.bathroom, a.exclusive,
228: a.built_size, a.built_size_unit,
229: a.land_size, a.land_size_unit,
230: a.owner_ads_title as ads_title,
231: unix_timestamp( ifnull(a.inf_mod_dt, a.inf_cre_dt) ) as listing_info_timestamp,
232: unix_timestamp( ifnull(b.inf_mod_dt, b.inf_cre_dt) ) as listing_photo_timestamp,
233: b.file_path as listing_photo,
234: (
235: select count(*) as kira1 from lis_listing_file a1 where a1.id_listing = a.id_listing
236: ) as listing_photo_count,
237: a.id_ren as agent_id,
238: if (
239: trim( ifnull( d.nickname, '' ) ) <> '',
240: d.nickname,
241: d.name
242: ) agent_name,
243: d.ren_level as agent_ren_level, d.reg_no as agent_reg_no,
244: d.have_reg_tag as agent_have_reg_tag,
245: d.photo as agent_photo, d.mobile as agent_mobile,
246: d.user_name as agent_user_name,
247: unix_timestamp( ifnull(d.inf_mod_dt, d.inf_cre_dt) ) as agent_timestamp,
248: ap1.itm_desc as state,
249: ap2.itm_desc as area
250: from lis_list c
251: left outer join lis_listing a on a.id_listing = c.id_listing
252: left outer join lis_listing_file b on b.id_listing = a.id_listing and b.file_name = 'FilePhoto01'
253: left outer join lis_list c on c.id_listing = a.id_listing and c.type = 'OWN'
254: left outer join neg_agent d on d.id_ren = c.id_ren
255: left outer join neg_agent x on x.id_ren = a.id_ren
256: left outer join sys_param ap1 on ap1.itm_id = a.addr_id_state
257: left outer join sys_param ap2 on ap2.itm_id = a.addr_id_area
258: left outer join sys_param ap41 on ap41.itm_id = a.id_category
259: left outer join sys_param ap42 on ap42.itm_id = a.id_type
260: left outer join sys_param ap43 on ap43.itm_id = a.id_lot_type
261: where c.inf_data_status > 100
262: and (
263: c.type = 'OWN'
264: or
265: (
266: c.type = 'COMARKET'
267: and (
268: c.status = 'REQUEST' or c.status = 'AGREE'
269: )
270: )
271: )
272: and a.inf_data_status > 100
273: and a.publish = 'Y'
274: and ifnull(a.id_project, '') = ''
275: and a.status = 'AVAILABLE'
276: and c.type = 'OWN'
277: and ifnull(a.id_objective_sub, '') <> '2008'
278: and ifnull(a.publish_expired_dt, '') <> ''
279: and a.publish_expired_dt >= ?
280: and x.inf_data_status > 100
281: and c.id_ren = ?
282: order by publish_dt desc
283: limit 0, 24 |