def get_index_userprofile():
# TODO defensive code
username = request.args.get('username')
service_name = request.args.get('service_name')
index_key_list = request.args.get('index_key_list', '')
if index_key_list != '':
index_key_list = index_key_list.split(";")
sql_format = "select index_key, index_value from userprofile " + \
"where username='******' and service_name='%s' and index_key in (%s)"
sql = sql_format % (username, service_name, ",".join(["'%s'" % (k) for k in index_key_list]))
print sql
out = util.query_sql(sql)
out = util.fill_missing_data(out, index_key_list, '0')
else:
sql_format = "select index_key, index_value from userprofile " + \
"where username='******' and service_name='%s'"
sql = sql_format % (username, service_name)
print sql
out = util.query_sql(sql)
print out
content = render_template('get_userprofile.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def get_index_userprofile():
# TODO defensive code
username = request.args.get('username')
service_name = request.args.get('service_name')
index_key_list = request.args.get('index_key_list', '')
if index_key_list != '':
index_key_list = index_key_list.split(";")
sql_format = "select index_key, index_value from userprofile " + \
"where username='******' and service_name='%s' and index_key in (%s)"
sql = sql_format % (username, service_name, ",".join(
["'%s'" % (k) for k in index_key_list]))
print sql
out = util.query_sql(sql)
out = util.fill_missing_data(out, index_key_list, '0')
else:
sql_format = "select index_key, index_value from userprofile " + \
"where username='******' and service_name='%s'"
sql = sql_format % (username, service_name)
print sql
out = util.query_sql(sql)
print out
content = render_template('get_userprofile.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def get_usertext():
# TODO defensive code
username = request.args.get('username')
service_name = request.args.get('service_name')
limit = 50 # TODO: remove hard code here
sql_format = "select createtime, content from usertext " + \
"where from_username in ('%s', '%s') and to_username in ('%s', '%s') order by id desc limit %d;"
sql = sql_format % (username, service_name, username, service_name, limit)
print sql
out = util.query_sql(sql)
content = render_template('get_usertext.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def get_user_latest_inputtext():
# TODO defensive code
service_name = request.args.get('service_name')
limit = 35 # TODO: remove hard code here
# "where from_username not in ('obF30jr0VD4HUjUq1kYusd5gSCBo', 'obF30jvBV656EFFzbzFoqMGxPivM') and " + \
sql_format = "select createtime, from_username, content from usertext " + \
"where msgid!='' and to_username='******' order by createtime desc limit %d;"
sql = sql_format % (service_name, limit)
print sql
out = util.query_sql(sql)
content = render_template('get_user_latest_inputtext.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def get_usertext():
# TODO defensive code
username = request.args.get('username')
service_name = request.args.get('service_name')
limit = 50 # TODO: remove hard code here
sql_format = "select createtime, content from usertext " + \
"where from_username in ('%s', '%s') and to_username in ('%s', '%s') order by id desc limit %d;"
sql = sql_format % (username, service_name, username, service_name, limit)
print sql
out = util.query_sql(sql)
content = render_template('get_usertext.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def get_user_latest_inputtext():
# TODO defensive code
service_name = request.args.get('service_name')
limit = 35 # TODO: remove hard code here
# "where from_username not in ('obF30jr0VD4HUjUq1kYusd5gSCBo', 'obF30jvBV656EFFzbzFoqMGxPivM') and " + \
sql_format = "select createtime, from_username, content from usertext " + \
"where msgid!='' and to_username='******' order by createtime desc limit %d;"
sql = sql_format % (service_name, limit)
print sql
out = util.query_sql(sql)
content = render_template('get_user_latest_inputtext.json', objs=out)
content = util.smarty_remove_extra_comma(content)
resp = make_response(content, 200)
resp.headers['Content-type'] = 'application/json; charset=utf-8'
return resp
def detect_daily_uniq_user_count(msg, msg_dict):
if not (msg_dict['MsgType'] == 'text' and msg_dict.has_key('MsgId')):
return True
conn = MySQLdb.connect(config.DB_HOST, config.DB_USERNAME, \
config.DB_PWD, config.DB_NAME, charset = 'utf8mb4', port = config.DB_PORT)
date = util.seconds_to_date(msg_dict['CreateTime'])
username = ''
service_name = msg_dict['ToUserName']
index_key = "%s_uniq_user_count" % (date)
# TODO: need to improve performance, instead of query the whole dataset
sql_format = "select count(1) from userprofile " + \
"where index_key = '%s_visit_count' and service_name = '%s';"
sql = sql_format % (date, service_name)
out = util.query_sql(conn, sql)
if len(out) == 0:
index_value = 0
else:
index_value = out[0][0] + 0
util.update_serviceprofile(service_name, index_key, index_value)
def detect_daily_uniq_user_count(msg, msg_dict):
if not (msg_dict["MsgType"] == "text" and msg_dict.has_key("MsgId")):
return True
conn = MySQLdb.connect(
config.DB_HOST, config.DB_USERNAME, config.DB_PWD, config.DB_NAME, charset="utf8mb4", port=config.DB_PORT
)
date = util.seconds_to_date(msg_dict["CreateTime"])
username = ""
service_name = msg_dict["ToUserName"]
index_key = "%s_uniq_user_count" % (date)
# TODO: need to improve performance, instead of query the whole dataset
sql_format = "select count(1) from userprofile " + "where index_key = '%s_visit_count' and service_name = '%s';"
sql = sql_format % (date, service_name)
out = util.query_sql(conn, sql)
if len(out) == 0:
index_value = 0
else:
index_value = out[0][0] + 0
util.update_serviceprofile(service_name, index_key, index_value)