SELECT
                SQL_CALC_FOUND_ROWS
				ot.id,
				ot.code,
				ot.disp_flag,
				ot.disp_from,
				ot.disp_to,
				ot.name,
				ot.disp_name,
				ot.name_en,
				ot.title_tag,
				ot.recommend_flag,
				ot.country_level_recommend_flag,
				ot.sales_flag,
				ot.best_seller_flag,
				ot.top_page_recommend_flag,
				ot.group_no,
				ot.order_no,
				ot.option_type,
				ot.unit_type,
				ot.toshicd,
				ot.handling_company_cd,
				ot.arranging_company_cd,
				ot.currency_cd,
				ot.theme,
				ot.icon,
				ot.activity_duration,
				ot.activity_duration_text,
				ot.hour,
				ot.main_image,
				ot.main_video,
				ot.summary,
				ot.notice,
				ot.appliance,
				ot.attention,
				ot.departure_date_input_flag,
				ot.input_flight_flag,
				ot.delete_flag,
				tm.webname AS toshi_name,
				tm.webnamee AS toshi_namee,
                tm.main_img AS toshi_img,
                tm.opsite_text AS toshi_text,
				IF(tm.pref_cd !='',concat('JP-',tm.pref_cd),hm.code) as homencd,
				IF(tm.pref_cd !='',prf.prefecture_name,hm.webname) as homen_name,
				IF(tm.pref_cd !='',prf.prefecture_namee,hm.webnamee) AS homen_namee,
                hm.main_img AS homen_img,
                IF(tm.pref_cd !='' && prf.opsite_text !='',prf.opsite_text,hm.opsite_text) AS homen_text,
				IF(tm.pref_cd !='','JP',hgm.code) as homengcd,
				IF(tm.pref_cd !='','日本',hgm.webname) as homeng_name,
                op2.plan_code,
                op2.plan_name,
                op2.detail as plan_detail,
                op2.main_image as plan_image,
                min(dp.min_price) as min_price,
                truncate(avg(rv.rating),1) as review_rate ,
				group_concat(distinct(rv.id)) as review_ids,
				IF(tm.pref_cd !='',1,0) as jpmode,
				CASE 
				WHEN tm2.main_img = 1 THEN CONCAT(prf.op_image_homen,'/',op_image_city)
				WHEN hm2.main_img = 1 THEN prf.op_image_homen 
				ELSE '' END as jp_homen_img,
				IF(tm.pref_cd !='' AND tm.main_img = 1,concat(hm.code,'/',toshicd),'') as jp_toshi_img
			FROM option_heads ot
            
                INNER JOIN ( select option_code,min(disp_no) as disp_no from option_plans where delete_flag = 0 AND disp_from <= CURRENT_DATE AND disp_to >= CURRENT_DATE AND disp_flag = 1 group by option_code) op
                ON op.option_code = ot.code
                INNER JOIN option_plans op2 ON op2.option_code = ot.code AND op2.disp_no = op.disp_no
            
			INNER JOIN option_dateprices dp ON op2.plan_code = dp.plan_code AND dp.type = 'A'
			AND CONCAT(dp.year,lpad(dp.month,2,'0')) >= DATE_FORMAT( CURRENT_DATE + INTERVAL IFNULL(op2.reservation_limit,0) day,'%Y%m')
            
            LEFT JOIN reviews rv ON rv.option_code = ot.code AND rv.status = 1 AND rv.publish_date <= CURRENT_DATE
			LEFT JOIN toshi_masters tm ON ot.toshicd = tm.code
			LEFT JOIN homen_masters hm ON tm.homencd = hm.code
			LEFT JOIN homeng_masters hgm ON hm.homengcd = hgm.code
			LEFT JOIN prefecture prf ON prf.prefecture_code = tm.pref_cd
			LEFT JOIN toshi_masters tm2 ON tm2.code = prf.op_image_city
			LEFT JOIN homen_masters hm2 ON hm2.code = prf.op_image_homen
            WHERE ot.delete_flag = 0
            AND ot.disp_from <= CURRENT_DATE AND ot.disp_to >= CURRENT_DATE AND ot.disp_flag = 1 AND ot.detail_only = 0 
		 AND ot.display_b2b_flag = 1  AND  tm.homencd = :homen AND toshicd = :toshicd AND find_in_set(hm.code,:displayHomens)  group by ot.code  order by ot.group_no desc,order_no desc  LIMIT 12 OFFSET 0