def getHotelsInDistrict(district_id, filters, star_number):
sql = "select id, name, address, logo, star_number from roothotel_info where district_id = " + str(
district_id)
if star_number != "6":
star_number = list(set(map(int, star_number.split('_'))))
star_number.sort()
sql_where = ""
for s in star_number:
if sql_where == "":
sql_where = " and (star_number=" + str(s)
else:
sql_where += " or star_number=" + str(s)
sql += sql_where + ")"
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql))
# print(df)
# return df
FinalMapping = pd.read_csv('FinalMapping.csv')
if filters != "15":
# print("hghj")
domain_hotel_mapping_ids = sort_and_filter.handle_filter(filters)
df_result = pd.DataFrame(
FinalMapping[FinalMapping['domain_hotel_mapping_id'].isin(
domain_hotel_mapping_ids)])
hotel_ids = df_result['hotel_id'].values
df = pd.DataFrame(df[df[0].isin(hotel_ids)])
else:
hotel_ids = FinalMapping['hotel_id'].values
df = pd.DataFrame(df[df[0].isin(hotel_ids)])
print(df)
# print("//////////////////////////////////////////////////////////")
return df
def searchDistrict(string):
string = "%%" + string + "%%"
string = handle_input.string_no_accent(string)
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(
phoenix_db.execute(
"select id, name from district where name_no_accent like \'" +
string + "\'"))
return df
def getMinPrice(domain_hotel_id):
try:
sql = "Select final_amount_min from hotel_price_daily where domain_hotel_id = {0} order by final_amount_min limit 1".format(
domain_hotel_id)
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql))
return df[0][0]
except:
return -1
def getDescription(hotel_id):
df = getAllId(hotel_id)
# print(df)
domain_hotel_mapping_id = df['domain_hotel_mapping_id'].tolist()[-1]
print(domain_hotel_mapping_id)
sql = "select description from hotel_info where hotel_id = \'" + domain_hotel_mapping_id + "\'"
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql))
return (df[0][0])
def getInformation(hotel_id):
sql = "select id, name, address, logo, latitude, longitude from roothotel_info where id = " + str(
hotel_id)
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql),
columns=[
'hotel_id', 'name', 'address', 'logo', 'latitude',
'longitude'
])
return df
def searchHotelName(string):
string = "%%" + string + "%%"
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(
phoenix_db.execute(
"select id, name from roothotel_info where name like \'" + string +
"\'"))
FinalMapping = pd.read_csv('FinalMapping.csv')
hotel_ids = FinalMapping['hotel_id'].values
df = pd.DataFrame(df[df[0].isin(hotel_ids)])
return df
def getURL(domain_hotel_id):
phoenix_db = load_data.SqlCommon()
sql = "Select url from hotel_info where domain_hotel_id = {0}".format(
domain_hotel_id)
df = pd.DataFrame(phoenix_db.execute(sql))
try:
return df[0][0]
except:
return -1
def getReviews(hotel_id):
FinalMapping = pd.read_csv('FinalMapping.csv')
df = pd.DataFrame(FinalMapping[FinalMapping['hotel_id'] == hotel_id]
['domain_hotel_mapping_id'])
df['domain_hotel_id'] = 0
for index, row in df.iterrows():
df.loc[df['domain_hotel_mapping_id'] == row['domain_hotel_mapping_id'],
'domain_hotel_id'] = getDomainHotelId(
row['domain_hotel_mapping_id'])
df = df.drop_duplicates(subset='domain_hotel_id', keep="first")
domain_hotel_ids = df['domain_hotel_id'].values.tolist()
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(
phoenix_db.execute(
"select review_datetime, title, text, score from hotel_review where lang_code = 'vi' and domain_hotel_id in "
+ str(domain_hotel_ids) + " limit 5"))
return df
def handle_filter(filters):
filters = list(set(map(int, filters.split('_'))))
filters.sort()
sql = "select hm.id from hotel_mapping hm join hotel_quality hq on hm.id = hq.hotel_id join hotel_facility hf on hm.id = hf.hotel_id join hotel_info hi on hm.id = hi.hotel_id join hotel_service hs on hm.id = hs.hotel_id"
sql_where = ""
array_filter = [
"", "currency_exchange = 1", "room_service_24_hour = 1",
"elevator = 1", "safely_deposit_boxed = 1", "luggage_storage = 1",
"airport_transfer = 1", "restaurants = 1", "concierge = 1",
"front_desk_24_hour = 1", "bar = 1", "laundry_service = 1",
"(is_free_car_park = 1 or is_internet_free_wifi_in_area = 1 or is_internet_free_wifi_in_room = 1)",
"tours = 13", "relax_outdoor_pool = 1"
]
for f in filters:
if (sql_where == ""):
sql_where = " where " + array_filter[f]
else:
sql_where += " and " + array_filter[f]
sql += sql_where
phoenix_db = load_data.SqlCommon()
df_filter = pd.DataFrame(phoenix_db.execute(sql))
return df_filter[0].values
def getDomainHotelId(domain_hotel_mapping_id):
sql = "select domain_hotel_id from hotel_mapping where id = \'" + domain_hotel_mapping_id + "\'"
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql))
return df[0][0]
def getHotelsWithName(hotel_id):
sql = "select id, name, address, logo, star_number from roothotel_info where id = " + str(
hotel_id)
phoenix_db = load_data.SqlCommon()
df = pd.DataFrame(phoenix_db.execute(sql))
return df
