def calculate_circulation_times():
circulation_coin = calculate_currency()
necessary_coin = 0
sql = "select lovecoin from coin_exchange where to_days(time) = to_days(now())"
try:
exchange_result = dbhelper.execute_fetchall(sql)
for each_result in exchange_result:
necessary_coin += each_result[0]
except:
return None
sql_event = "select love_coin from event where to_days(time) = to_days(now())"
try:
event_result = dbhelper.execute_fetchall(sql_event)
for each_result in event_result:
necessary_coin += each_result[0]
except:
return None
if necessary_coin == 0:
return 0
else:
return ('%.2f' % (circulation_coin / necessary_coin))
def get_neighbor(data):
neighbor_uid_list = []
if KEY.ID not in data:
return neighbor_uid_list
user = get_user_information(data)
if user is None:
return neighbor_uid_list
DISTANCE = 15.0 # 15000m
location_range = haversine.get_range(user[KEY.LONGITUDE], user[KEY.LATITUDE], DISTANCE)
sql = "select id from user where " \
"longitude > %f and longitude < %f " \
"and latitude > %f and latitude < %f"
sql_result = dbhelper.execute_fetchall(
sql%(location_range[0], location_range[1], location_range[2], location_range[3]))
if KEY.TYPE in data:
for each_result in sql_result:
user = {}
user[KEY.ID] = each_result[0]
user = get_user_information(user)
if user is not None:
neighbor_uid_list.append(user)
else:
for each_result in sql_result:
user = {}
user[KEY.ID] = each_result[0]
user = get_user_information(user)
if user is not None:
neighbor_uid_list.append(user[KEY.IDENTITY_ID])
return neighbor_uid_list
def get_event_supporter(data):
supporter_list = []
if KEY.ID not in data or KEY.EVENT_ID not in data:
return support_list
# 1. find supporters id in table 'support_relation' by launcher id and event id
find_id_sql = "select supporter from support_relation where supportee = %d and event_id = %d"
id_result = dbhelper.execute_fetchall(find_id_sql%(data[KEY.ID], data[KEY.EVENT_ID]))
# 2. find accounts from table 'account' by ids
find_account_sql = "select account from account where id = %d"
if id_result is not None:
for each_id in id_result:
supporter_result = dbhelper.execute_fetchone(find_account_sql%(each_id[0]))
print each_id
supporter = {}
supporter[KEY.ACCOUNT] = supporter_result[0]
user_info = get_user_information(supporter)
# get current location of supporter
find_longitude_sql = "select longitude from support_relation where event_id = %d and supporter = %d"
find_latitude_sql = "select latitude from support_relation where event_id = %d and supporter = %d"
longitude_result = dbhelper.execute_fetchone(find_longitude_sql%(data[KEY.EVENT_ID], each_id[0]))
latitude_result = dbhelper.execute_fetchone(find_latitude_sql%(data[KEY.EVENT_ID], each_id[0]))
if user_info is not None:
supporter.update(user_info)
supporter[KEY.ACCOUNT] = supporter_result[0]
supporter[KEY.LONGITUDE] = float(longitude_result[0])
supporter[KEY.LATITUDE] = float(latitude_result[0])
supporter_list.append(supporter)
#supporter_list.append(supporter_result[0])
return supporter_list
def get_static_relation(data):
# related user list
user_list = []
# a related user, include account(table 'account'), nickname, avatar(table 'user')
one_user = {}
if KEY.ID not in data:
return user_list
# 1. get a user_id_list from table 'static_relation'
get_id_list_sql = "select user_b from static_relation where user_a = %d"%data[KEY.ID]
if KEY.TYPE in data:
if data[KEY.TYPE] == 1 or data[KEY.TYPE] == 0:
get_id_list_sql += " and type = %d"%data[KEY.TYPE]
sql_result = dbhelper.execute_fetchall(get_id_list_sql)
user_id_list = []
for each_result in sql_result:
for each_id in each_result:
user_id_list.append(each_id)
# 2. get each user's information, put them in to user list
for each_user in user_id_list:
one_user[KEY.ID] = each_user
one_user = get_user(one_user)
if one_user is not None:
one_user[KEY.ID] = each_user
user_list.append(one_user)
return user_list
def algorithm_analysis():
'''
时间范围：30天内
type为1或者2，这是求助，或者求救事件，才有爱心币
state：2 说明为正常结束事件的。
'''
sql = "select id from event where date_sub(curdate(),interval 30 day) <= date(time) and (type = 1 or type = 2) and state = 2"
earn_coin = 0
try:
result = dbhelper.execute_fetchall(sql)
for each_result in result:
user_num = get_supporter_num_from_event(each_result[0])
event_coin = get_coin_from_event(each_result[0])
if user_num is None or event_coin is None:
return None
if user_num == 0:
earn_coin += 0
else:
earn_coin += event_coin * (user_num - 1)
except:
return None
basic_coin = calcultae_system_lovecoin() - earn_coin
analyse_result = (earn_coin * 1.0) / basic_coin
return '%.4f' % analyse_result
def calculate_currency_weekly():
result = []
sql_exchange = "select lovecoin from coin_exchange where to_days(now())-to_days(time) <= %d"
sql_trade = "select lovecoin from coin_trade where to_days(now())-to_days(time) <= %d"
result.append(calculate_currency())
try:
for num in range(0, 6):
exchange = 0
trade = 0
temp1 = 0
exchange_result1 = dbhelper.execute_fetchall(sql_exchange % (num))
for each_result in exchange_result1:
temp1 += each_result[0]
temp2 = 0
exchange_result2 = dbhelper.execute_fetchall(sql_exchange %
(num + 1))
for each_result in exchange_result2:
temp2 += each_result[0]
exchange = temp2 - temp1
temp3 = 0
trade_result1 = dbhelper.execute_fetchall(sql_trade % (num))
for each_result in trade_result1:
temp3 += trade_result1[0]
temp4 = 0
trade_result2 = dbhelper.execute_fetchall(sql_trade % (num + 1))
for each_result in trade_result2:
temp4 += each_result[0]
trade = temp4 - temp3
result.append(exchange + trade)
return result
except:
return None
def get_answer_id_list(data):
answer_id_list = []
if KEY.EVENT_ID not in data:
return answer_id_list
sql = "select id from answer where event_id = %d"%data[KEY.EVENT_ID]
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
answer_id_list.append(each_id)
return answer_id_list
def get_event_followers(data):
followers_id_list = []
if KEY.EVENT_ID not in data:
return followers_id_list
sql = "select supporter from support_relation where event_id = %d"%data[KEY.EVENT_ID]
sql += " and type = 2"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
followers_id_list.append(each_id)
return followers_id_list
def connect_hx():
sql = "select * from account"
user_list = []
user_info = {}
sql_result = dbhelper.execute_fetchall(sql)
if sql_result is not None:
for each_result in sql_result:
user_info['username'] = each_result[KEY.ID]
user_info[KEY.PASSWORD] = each_result[KEY.PASSWORD]
if user_info is not None:
user_list.append(user_info)
return user_list
def calcultae_system_lovecoin():
result = 0
sql = "select love_coin from loving_bank"
try:
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
result += each_result[0]
return result
except:
return None
def get_questionlist(data):
question_list = []
question = {}
sql = "select * from question"
try:
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
question[KEY.QUESTION_ID] = each_id
question = get_question_information(question)
if question is not None:
question_list.append(question)
return question_list
except:
return None
def calclulate_user_involve_lovecoin():
result = set()
sql = "select sender,receiver from coin_exchange where to_days(time) = to_days(now())"
try:
temp1 = dbhelper.execute_fetchall(sql)
for each_result in temp1:
result.add(each_result[0])
result.add(each_result[1])
except:
return None
sql = "select sender,receiver from coin_trade where to_days(time) = to_days(now())"
try:
temp2 = dbhelper.execute_fetchall(sql)
for each_result in temp2:
result.add(each_result[0])
result.add(each_result[1])
except:
return None
return len(result)
def get_friendlist_information(data):
friend_list = []
friend = {}
sql = "select guest_name from friendlist where host_name = %d order by time DESC"
try:
sql_result = dbhelper.execute_fetchall(sql%(data[KEY.HOST_NAME]))
for each_result in sql_result:
for each_id in each_result:
friend[KEY.HOST_NAME] = each_id
friend = get_friend(friend)
if friend is not None:
friend_list.append(friend)
return friend_list
except:
return None
def get_join_event_list(data):
event_id_list = []
if KEY.ID not in data:
return event_id_list
sql = "select event_id from support_relation where supporter = %d"%data[KEY.ID]
if KEY.TYPE in data:
if data[KEY.TYPE] == 1 or data[KEY.TYPE] == 2:
sql += " and type = %d"%data[KEY.TYPE]
sql += " order by time DESC"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
event_id_list.append(each_id)
return event_id_list
def calculate_currency():
trade = 0
exchange = 0
sql = "select lovecoin from coin_exchange where to_days(time) = to_days(now())"
try:
exchange_result = dbhelper.execute_fetchall(sql)
for each_result in exchange_result:
exchange += each_result[0]
except:
return None
sql = "select lovecoin from coin_trade where to_days(time) = to_days(now())"
try:
trade_result = dbhelper.execute_fetchall(sql)
for each_result in trade_result:
trade += each_result[0]
except:
return None
return exchange + trade
def get_supporters(data):
sup_list = []
if KEY.EVENT_ID not in data:
return sup_list
sql = "select supporter from support_relation where event_id = %d"%data[KEY.EVENT_ID]
if KEY.TYPE in data:
sql += " and type = %d"%data[KEY.TYPE]
sql_result = dbhelper.execute_fetchall(sql)
resp = {}
for each_result in sql_result:
for each_id in each_result:
resp[KEY.ID] = each_id
resp = get_user_information(resp)
sup_list.append(resp)
return sup_list
def get_join_event_list(data):
event_id_list = []
if KEY.ID not in data:
return event_id_list
sql = "select event_id from support_relation where supporter = %d" % data[
KEY.ID]
if KEY.TYPE in data:
if data[KEY.TYPE] == 1 or data[KEY.TYPE] == 2:
sql += " and type = %d" % data[KEY.TYPE]
sql += " order by time DESC"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
event_id_list.append(each_id)
return event_id_list
def get_comments(data):
if KEY.EVENT_ID not in data:
return None
comment_list = []
comment = {}
sql = "select id from comment where event_id = %d order by time DESC"
try:
sql_result = dbhelper.execute_fetchall(sql % (data[KEY.EVENT_ID]))
for each_result in sql_result:
for each_id in each_result:
comment[KEY.COMMENT_ID] = each_id
comment = get_comment_info(comment)
if comment is not None:
comment_list.append(comment)
return comment_list
except:
return None
def welfare_coin(data):
sql = "select userid from loving_bank where love_coin <= 5"
try:
result = dbhelper.execute_fetchall(sql)
for each_result in result:
info = {}
info[KEY.ID] = each_result[0]
info[KEY.OPERATION] = 0
info[KEY.SCORE] = 0
info[KEY.LOVE_COIN] = data
if (not db.update_loving_bank(info)):
return None
return True
except:
return None
def get_comments(data):
if KEY.EVENT_ID not in data:
return None
comment_list = []
comment = {}
sql = "select id from comment where event_id = %d order by time DESC"
try:
sql_result = dbhelper.execute_fetchall(sql % (data[KEY.EVENT_ID]))
for each_result in sql_result:
for each_id in each_result:
comment[KEY.COMMENT_ID] = each_id
comment = get_comment_info(comment)
if comment is not None:
comment_list.append(comment)
return comment_list
except:
return None
def get_launch_event_list(data):
event_id_list = []
if KEY.ID not in data:
return event_id_list
sql = "select id from event where launcher = %d" % data[KEY.ID]
if KEY.STATE in data:
if data[KEY.STATE] == 0 or data[KEY.STATE] == 1:
sql += " and state = %d" % data[KEY.STATE]
if KEY.TYPE in data:
if data[KEY.TYPE] >= 0 and data[KEY.TYPE] <= 2:
sql += " and type = %d" % data[KEY.TYPE]
sql += " order by time DESC"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
event_id_list.append(each_id)
return event_id_list
def get_illness_records(data):
if KEY.ID not in data:
return None
sql = "select id from illness where user_id = %d order by time ASC limit %d"
sql_result = None
records = None
try:
sql_result = dbhelper.execute_fetchall(sql%(data[KEY.ID], 100))
except:
sql_result = None
if sql_result is not None:
records = []
for each_result in sql_result:
for each_id in each_result:
a_record = get_illness_record(each_id)
if a_record is not None:
records.append(a_record)
return records
def get_launch_event_list(data):
event_id_list = []
if KEY.ID not in data:
return event_id_list
sql = "select id from event where launcher = %d" % data[KEY.ID]
if KEY.STATE in data:
if data[KEY.STATE] == 0 or data[KEY.STATE] == 1:
sql += " and state = %d" % data[KEY.STATE]
if KEY.TYPE in data:
if data[KEY.TYPE] >= 0 and data[KEY.TYPE] <= 2:
sql += " and type = %d" % data[KEY.TYPE]
sql += " order by time DESC"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
event_id_list.append(each_id)
return event_id_list
def get_illness_records(data):
if KEY.ID not in data:
return None
sql = "select id from illness where user_id = %d order by time ASC limit %d"
sql_result = None
records = None
try:
sql_result = dbhelper.execute_fetchall(sql % (data[KEY.ID], 100))
except:
sql_result = None
if sql_result is not None:
records = []
for each_result in sql_result:
for each_id in each_result:
a_record = get_illness_record(each_id)
if a_record is not None:
records.append(a_record)
return records
def query_follow(data):
if KEY.ID not in data and KEY.STATE not in data:
return user_list
if data[KEY.STATE] == 0:
sql = "select user_b from static_relation where user_a = %d"%data[KEY.ID]
elif data[KEY.STATE] == 1:
sql = "select user_a from static_relation where user_b = %d"%data[KEY.ID]
else:
return -1
user_list = []
if KEY.TYPE in data:
if data[KEY.TYPE] == 0 or data[KEY.TYPE] ==1 or data[KEY.TYPE] == 2:
sql += " and type = %d"%data[KEY.TYPE]
sql_result = dbhelper.execute_fetchall(sql)
resp = {}
for each_result in sql_result:
for each_id in each_result:
resp[KEY.ID] = each_id
resp = get_user_information(resp)
user_list.append(resp)
return user_list
def get_nearby_event(data):
nearby_event_list = []
if KEY.ID not in data:
return nearby_event_list
user = get_user_information(data)
DISTANCE = 2.0 # 2000m
location_range = haversine.get_range(user[KEY.LONGITUDE], user[KEY.LATITUDE], DISTANCE)
sql = "select id from event where " \
"longitude > %f and longitude < %f " \
"and latitude > %f and latitude < %f"\
%(location_range[0], location_range[1], location_range[2], location_range[3])
if KEY.TYPE in data:
sql += " and type = %d"%data[KEY.TYPE]
if KEY.STATE in data:
sql += " and state = %d"%data[KEY.STATE]
if KEY.LAST_TIME in data:
sql += " and last_time > '%s'"%data[KEY.LAST_TIME]
sql += " order by time DESC"
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
for each_id in each_result:
nearby_event_list.append(each_id)
return nearby_event_list
def user_consume_coin():
result = 0
sql = "select love_coin from event"
try:
sql_result = dbhelper.execute_fetchall(sql)
for each_result in sql_result:
result += each_result[0]
except:
return None
sql_event_num = "select count(*) from event"
try:
event_num = dbhelper.execute_fetchone(sql_event_num)[0]
except:
return None
if event_num == 0:
return 0
else:
return ('%.2f' % (result / event_num))
