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_concept_english():
db = Database()
t = BingTranslate()
cmd = "SELECT * FROM concept"
concept_res = db.query_db(cmd)
for concept in concept_res[90835:]:
concept_id = concept[0]
concept_name = concept[1]
english = t.translate(concept_name, "zh-CHT", "en").lower()
print english.encode("utf-8")
cmd = 'UPDATE concept SET english = "%s" WHERE id = %s' % (english, concept_id)
db.query_db(cmd)
def download_concept_audio():
"""
Download audio wav file for each concept.
"""
db = Database()
t = BingTranslate()
cmd = "SELECT * FROM concept"
concept_res = db.query_db(cmd)
for concept in concept_res[117599:]:
concept_id = concept[0]
concept_name = concept[1]
filename = path + "static/audio/concept/" + str(concept_id) + ".wav"
print filename
t.get_speech(concept_name, filename, "zh-CHT")
def get_concept_dialog(concept, location, action, user_id):
json = {}
cmd = "SELECT id, english FROM concept WHERE name = '%s'" % (concept)
concept_res = db.query_db(cmd)
concept_id = concept_res[0][0]
concept_english = concept_res[0][1]
cmd = "SELECT id FROM location WHERE chinese_name = '%s'" % (location)
location_id = db.query_db(cmd)[0][0]
# check dialog for action first
if action != u'任何事':
cmd = "SELECT id FROM action WHERE chinese_name = '%s'" \
% (action)
else:
cmd = "SELECT action_id FROM location_action WHERE location_id = %s" \
% (location_id)
for action_item in db.query_db(cmd):
action_id = action_item[0]
cmd = "SELECT category FROM action_concept WHERE \
action_id = %s AND concept_id = %s" % (action_id, concept_id)
category_res = db.query_db(cmd)
if len(category_res) != 0:
category = category_res[0][0]
cmd = "SELECT b.id, b.content FROM dialog_action_category AS a, \
dialog AS b WHERE a.dialog_id = b.id AND \
a.action_id = %s AND a.category = %s ORDER BY b.difficulty" \
% (action_id, category)
dialog_res = db.query_db(cmd)
for dialog_item in dialog_res:
dialog_id = dialog_item[0]
dialog = dialog_item[1]
cmd = "SELECT a.view_count+COUNT(b.concept_id) FROM \
user_dialog AS a, user_location_dialog AS b WHERE \
a.user_id = %s AND b.user_id = %s AND a.dialog_id = %s \
AND b.dialog_id = %s" % (user_id, user_id, dialog_id, dialog_id)
view_res = db.query_db(cmd)
if len(view_res) > 0:
if view_res[0][0] > 5:
continue
matches = re.findall(r'\[\w+\]', \
dialog, flags=re.UNICODE)
matches = list(set(matches))
for match in matches:
match = match.replace('[', '').replace(']', '')
cmd = "SELECT a.category, b.english FROM \
action_concept AS a, concept AS b \
WHERE a.action_id = %s AND b.name = '%s' AND \
a.concept_id = b.id" % (action_id, match)
category_res = db.query_db(cmd)
if len(category_res) > 0:
if category == category_res[0][0]:
json['dialog_id'] = dialog_id
json['dialog'] = []
dialog = dialog.lower().replace('['+category_res[0][1]+']', '['+concept_english+']')
print category_res[0][1]
print concept_english
t = BingTranslate()
for sentence in \
dialog.replace('['+match+']', '['+concept+']').split('\n'):
chinese = sentence.split('\t')[0]
english = sentence.split('\t')[1]
cmd = "SELECT id FROM sentence WHERE sentence = '%s'" % (chinese)
sentence_res = db.query_db(cmd)
if len(sentence_res) == 0:
cmd = "INSERT INTO sentence (sentence) VALUES ('%s')" % (chinese)
db.query_db(cmd)
cmd = "SELECT id FROM sentence WHERE sentence = '%s'" % (chinese)
sentence_res = db.query_db(cmd)
filename = '/home/a33kuo/public_html/language-learner/audio/dialog/'+str(sentence_res[0][0])+'.wav'
t.get_speech(chinese.replace('[', '').replace(']', ''), filename, "zh-CHT")
audio_file = 'http://lime.csie.ntu.edu.tw/~a33kuo/language-learner/audio/dialog/'+str(sentence_res[0][0])+'.wav'
json['dialog'].append(\
{'chinese':chinese, 'english':english, 'audio':audio_file})
break
if len(json.keys()) > 0:
return flask.jsonify(json)
# if there is no dialog for action, use location
cmd = "SELECT category FROM location_concept WHERE \
location_id = %s AND concept_id = %s" % (location_id, concept_id)
category_res = db.query_db(cmd)
if len(category_res) != 0:
category = category_res[0][0]
cmd = "SELECT b.id, b.content FROM dialog_location_category AS a, \
dialog AS b WHERE a.dialog_id = b.id AND \
a.location_id = %s AND a.category = %s ORDER BY b.difficulty" \
% (location_id, category)
dialog_res = db.query_db(cmd)
for dialog_item in dialog_res:
dialog_id = dialog_item[0]
dialog = dialog_item[1]
cmd = "SELECT a.view_count+COUNT(b.concept_id) FROM \
user_dialog AS a, user_location_dialog AS b WHERE \
a.user_id = %s AND b.user_id = %s AND a.dialog_id = %s \
AND b.dialog_id = %s" % (user_id, user_id, dialog_id, dialog_id)
view_res = db.query_db(cmd)
if len(view_res) > 0:
if view_res[0][0] > 5:
continue
matches = re.findall(r'\[\w+\]', \
dialog, flags=re.UNICODE)
matches = list(set(matches))
for match in matches:
match = match.replace('[', '').replace(']', '')
cmd = "SELECT a.category, b.english FROM \
location_concept AS a, concept AS b \
WHERE a.location_id = %s AND b.name = '%s' AND \
a.concept_id = b.id" % (location_id, match)
category_res = db.query_db(cmd)
if len(category_res) > 0:
if category == category_res[0][0]:
json['dialog_id'] = dialog_id
json['dialog'] = []
dialog = dialog.lower().replace('['+category_res[0][1]+']', '['+concept_english+']')
print category_res[0][1]
print concept_english
t = BingTranslate()
for sentence in \
dialog.replace('['+match+']', '['+concept+']').split('\n'):
chinese = sentence.split('\t')[0]
english = sentence.split('\t')[1]
cmd = "SELECT id FROM sentence WHERE sentence = '%s'" % (chinese)
sentence_res = db.query_db(cmd)
if len(sentence_res) == 0:
cmd = "INSERT INTO sentence (sentence) VALUES ('%s')" % (chinese)
db.query_db(cmd)
cmd = "SELECT id FROM sentence WHERE sentence = '%s'" % (chinese)
sentence_res = db.query_db(cmd)
filename = '/home/a33kuo/public_html/language-learner/audio/dialog/'+str(sentence_res[0][0])+'.wav'
t.get_speech(chinese.replace('[', '').replace(']', ''), filename, "zh-CHT")
audio_file = 'http://lime.csie.ntu.edu.tw/~a33kuo/language-learner/audio/dialog/'+str(sentence_res[0][0])+'.wav'
json['dialog'].append(\
{'chinese':chinese, 'english':english, 'audio':audio_file})
break
if len(json.keys()) > 0:
break
return flask.jsonify(json)
