def update_location_actions():
from re import escape
location = Location()
db = Database()
t = BingTranslate()
cmd = "SELECT * FROM location"
location_res = db.query_db(cmd)
for item in location_res:
location_id, location_name, location_chinese = item
for action in location.get_action_for_venue(location_chinese):
cmd = u"SELECT * FROM action WHERE chinese_name = '%s'" % (action)
action_res = db.query_db(cmd)
if len(action_res) > 0:
action_id = action_res[0][0]
is_bad = action_res[0][3]
else:
action_en = t.translate(action, "zh-CHT", "en")
cmd = (
u"INSERT INTO action (chinese_name, english_name) \
VALUES ('%s', '%s')"
% (escape(action), escape(action_en))
)
db.query_db(cmd)
cmd = u"SELECT * FROM action WHERE chinese_name = '%s'" % (action)
action_res = db.query_db(cmd)
action_id = action_res[0][0]
is_bad = action_res[0][3]
if is_bad != 1:
cmd = (
"INSERT INTO location_action (location_id, action_id) \
VALUES (%s, %s)"
% (location_id, action_id)
)
db.query_db(cmd)
def update_action_concepts():
"""
Get related concepts of an action and store it in database.
"""
location = Location()
db = Database()
cmd = "SELECT * FROM action"
action_res = db.query_db(cmd)
for item in action_res:
action_id, action_chinese, action_english, is_bad = item
if is_bad == 1:
continue
for concept in location.get_concept_for_venue(action_chinese):
cmd = u"SELECT * FROM concept WHERE name = '%s'" % (concept)
concept_res = db.query_db(cmd)
if len(concept_res) > 0:
concept_id = concept_res[0][0]
is_bad = concept_res[0][8]
else:
continue
if is_bad != 1:
cmd = (
"INSERT INTO action_concept (action_id, concept_id) \
VALUES (%s, %s)"
% (action_id, concept_id)
)
db.query_db(cmd)
def get_location(geo):
geo = geo.split(',')
geo_lat = geo[0]
geo_long = geo[1]
location = Location()
json = {}
venues = location.get_venues(geo_lat, geo_long)
output = []
cmd = "SELECT DISTINCT a.location_id, b.name FROM \
dialog_location_category AS a, location AS b \
WHERE a.location_id = b.id"
location_res = db.query_db(cmd)
valid_location = map(lambda x: x[1], location_res)
for chinese, display in venues:
cmd = "SELECT a.concept_id FROM location_concept AS a, \
location AS b WHERE a.location_id = b.id AND \
b.chinese_name = '%s'" % (chinese)
concept_res = db.query_db(cmd)
if (display in valid_location) and (len(concept_res) > 8):
output.append({'display':display, 'chinese':chinese})
cmd = "SELECT * FROM location"
location_res = db.query_db(cmd)
for venue in location_res:
json_venue = {'display':venue[1], 'chinese':venue[2]}
if json_venue['display'] not in map(lambda x: x['display'], output):
cmd = "SELECT a.concept_id FROM location_concept AS a, \
location AS b WHERE a.location_id = b.id AND \
b.chinese_name = '%s'" % (venue[2])
concept_res = db.query_db(cmd)
if (venue[1] in valid_location) and (len(concept_res) > 8):
output.append(json_venue)
json['locations'] = output
return flask.jsonify(json)