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: /* 'Office / Shop (Commercial)' */
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: a.id_objective = 2000,
201: a.asking_price,
202: a.asking_rental
203: ) as price,
204: if (
205: a.id_objective = 2000,
206: a.asking_price_previous,
207: a.asking_rental_previous
208: ) as price_previous,
209: a.diff_market_value_vs_asking_price,
210: if (
211: trim( ifnull( a.diff_market_value_vs_asking_price, '' ) ) <> '',
212: if (
213: a.diff_market_value_vs_asking_price < 0,
214: 'Y',
215: 'N'
216: ),
217: 'N'
218: ) as below_market,
219: a.room, a.bathroom, a.exclusive,
220: a.built_size, a.built_size_unit,
221: a.land_size, a.land_size_unit,
222: a.owner_ads_title as ads_title,
223: unix_timestamp( ifnull(b.inf_mod_dt, b.inf_cre_dt) ) as listing_photo_timestamp,
224: b.file_path as listing_photo,
225: (
226: select count(*) as kira1 from lis_listing_file a1 where a1.id_listing = a.id_listing
227: ) as listing_photo_count,
228: a.id_ren as agent_id,
229: if (
230: trim( ifnull( d.nickname, '' ) ) <> '',
231: d.nickname,
232: d.name
233: ) agent_name,
234: d.ren_level as agent_ren_level, d.reg_no as agent_reg_no,
235: d.have_reg_tag as agent_have_reg_tag,
236: d.photo as agent_photo, d.mobile as agent_mobile,
237: d.user_name as agent_user_name,
238: unix_timestamp( ifnull(d.inf_mod_dt, d.inf_cre_dt) ) as agent_timestamp,
239: ap1.itm_desc as state,
240: ap2.itm_desc as area
241: from lis_list c
242: left outer join lis_listing a on a.id_listing = c.id_listing
243: left outer join lis_listing_file b on b.id_listing = a.id_listing and b.file_name = 'FilePhoto01'
244: left outer join neg_agent d on d.id_ren = c.id_ren
245: left outer join neg_agent x on x.id_ren = a.id_ren
246: left outer join sys_param ap1 on ap1.itm_id = a.addr_id_state
247: left outer join sys_param ap2 on ap2.itm_id = a.addr_id_area
248: left outer join sys_param ap41 on ap41.itm_id = a.id_category
249: left outer join sys_param ap42 on ap42.itm_id = a.id_type
250: left outer join sys_param ap43 on ap43.itm_id = a.id_lot_type
251: where c.inf_data_status > 100
252: and (
253: c.type = 'OWN'
254: or
255: (
256: c.type = 'COMARKET'
257: and (
258: c.status = 'REQUEST' or c.status = 'AGREE'
259: )
260: )
261: )
262: and a.inf_data_status > 100
263: and a.publish = 'Y'
264: and ifnull(a.id_project, '') = ''
265: and a.status = 'AVAILABLE'
266: and b.file_name = 'FilePhoto01'
267: and ifnull(a.id_objective_sub, '') <> '2008'
268: and ifnull(a.publish_expired_dt, '') <> ''
269: /* and a.publish_expired_dt >= ? */
270: and x.inf_data_status > 100
271: and c.id_ren = ?
272: and a.id_objective = ?
273: order by publish_dt desc
274: limit 0, |