def get_user_information(data):
if KEY.ACCOUNT not in data:
return None
find_id = "select id from account where account = %d"
user_id = dbhelper.execute_fetchone(find_id%(data[KEY.ACCOUNT]))
sql = "select * from user where id = %d"
try:
res = dbhelper.execute_fetchone(sql%(user_id[0]))
if res is None:
return None
else:
user = {}
user[KEY.ID] = res[0]
user[KEY.NAME] = res[1]
user[KEY.NICKNAME] = res[2]
user[KEY.GENDER] = res[3]
user[KEY.AGE] = res[4]
user[KEY.PHONE] = res[5]
user[KEY.LOCATION] = res[6]
user[KEY.LONGITUDE] = float(res[7])
user[KEY.LATITUDE] = float(res[8])
user[KEY.OCCUPATION] = res[9]
user[KEY.REPUTATION] = float(res[10])
#user[KEY.AVATAR] = res[11]
user[KEY.IDENTITY_ID] = res[12]
user[KEY.IS_VERIFY] = res[14]
return user
except:
return None
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 user_event_manage(data):
if KEY.ID not in data or KEY.EVENT_ID not in data:
return False
if KEY.OPERATION not in data:
return True
if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2:
return False
sql = "select launcher from event where id = %d"
launcher_id = None
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID]))
if sql_result is not None:
launcher_id = sql_result[0]
except:
pass
if launcher_id is None:
return False
if data[KEY.OPERATION] == 0:
sql = "delete from support_relation where event_id = %d and supporter = %d"%(data[KEY.EVENT_ID], data[KEY.ID])
update_event_sql = "update event set support_number = support_number-1 where id = %d"%(data[KEY.EVENT_ID])
else:
sql = "insert into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())"%(data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION])
update_event_sql = "update event set support_number = support_number+1 where id = %d"%(data[KEY.EVENT_ID])
count_supporter = "select count(*) from support_relation where event_id = %d and supportee = %d"%(data[KEY.EVENT_ID], launcher_id)
help_max = "select help_max from event where id = %d and launcher = %d"%(data[KEY.EVENT_ID], launcher_id)
state_sql = "select state from event where id = %d and launcher = %d"%(data[KEY.EVENT_ID], launcher_id)
update_state = "update event set state = %d where id = %d"
try:
# update support_relation
p = dbhelper.execute(sql)
print "From user_event_manage_handler: attend result: %d"%p
if p == 0:
return False
# update event information
dbhelper.execute(update_event_sql)
# Check if people are enough for helping
supporters_result = dbhelper.execute_fetchone(count_supporter)
help_max_result = dbhelper.execute_fetchone(help_max)
state_result = dbhelper.execute_fetchone(state_sql)
if supporters_result[0] == help_max_result[0] and state_result[0] == 0:
# update event state to "supporter enough"
dbhelper.execute(update_state%(2, data[KEY.EVENT_ID]))
print "From db update help event:", supporters_result[0], help_max_result[0]
elif supporters_result[0] < help_max_result[0] and state_result[0] == 2:
# update event state to "ing"
dbhelper.execute(update_state%(0, data[KEY.EVENT_ID]))
except:
return False
#
# trust and reputation compute here.
#
return True
def calculate_number_of_login_weekly():
result = []
result.append(calculate_number_of_login())
sql = "select count(*) from sign_in where to_days(now())-to_days(time) <= %d"
try:
for num in range(0, 6):
temp1 = dbhelper.execute_fetchone(sql % (num))[0]
temp2 = dbhelper.execute_fetchone(sql % (num + 1))[0]
result.append(temp2 - temp1)
return result
except:
return None
def evaluate_user(data):
if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.EVENT_ID not in data:
return False
if KEY.ATTITUDE not in data or KEY.SKILL not in data or KEY.SATISFY not in data:
return False
# get a new average reputation value
value = 0.0
value = data[KEY.ATTITUDE] + data[KEY.SKILL] + data[KEY.SATISFY]
value /= 3.0
# get type of the event
get_type = "select type from event where id = %d"%data[KEY.EVENT_ID]
type_result = dbhelper.execute_fetchone(get_type)
event_type = type_result[0]
result = 0
# split user_account string into long array
user_account = data[KEY.USER_ACCOUNT].split(",")
# update each user's reputation in list
for each_account in user_account:
if (long(each_account) != 0):
# get id by account
find_user_id = "select id from account where account = %d"
user_id = dbhelper.execute_fetchone(find_user_id%(long(each_account)))
print "From evaluate_handler: %ld" % long(each_account)
# update a record in table 'evaluation'
sql = "replace into evaluation (event_id, `from`, `to`, value, time, comment) values (%d, %d, %d, %f, now(), '%s')"
# get an updated reputation value from table 'evaluation'
get_final_value = "select AVG(value) from evaluation where `to` = %d"
# update evaluatee's reputation in table 'user'
update_repu = "update user set reputation = %d where id = %d"
# update love coins in table 'loving_bank'
if event_type == 0:
update_coin = "update loving_bank set coin = coin+1 where id = %d"
if event_type == 1:
update_coin = "update loving_bank set coin = coin+2 where id = %d"
if event_type == 2:
update_coin = "update loving_bank set coin = coin+3 where id = %d"
try:
a = dbhelper.execute(sql%(data[KEY.EVENT_ID], data[KEY.ID], user_id[0], value, data[KEY.ASSESS]))
final_value = dbhelper.execute_fetchone(get_final_value%(user_id[0]))
dbhelper.execute(update_repu%(final_value[0], user_id[0]))
dbhelper.execute(update_coin%(data[KEY.EVENT_ID]))
result += 1
except:
pass
return result
def get_event_information(data):
if KEY.EVENT_ID not in data:
return None
event_info = None
sql = "select * from event where id = %d"
try:
sql_result = dbhelper.execute_fetchone(sql % (data[KEY.EVENT_ID]))
if sql_result is not None:
event_info = {}
event_info[KEY.EVENT_ID] = sql_result[0]
event_info[KEY.LAUNCHER_ID] = sql_result[1]
event_info[KEY.CONTENT] = sql_result[2]
event_info[KEY.TYPE] = sql_result[3]
event_info[KEY.TIME] = str(sql_result[4])
event_info[KEY.LONGITUDE] = float(sql_result[5])
event_info[KEY.LATITUDE] = float(sql_result[6])
event_info[KEY.STATE] = sql_result[7]
event_info[KEY.FOLLOW_NUMBER] = sql_result[8]
event_info[KEY.SUPPORT_NUMBER] = sql_result[9]
event_info[KEY.GROUP_PTS] = float(sql_result[10])
user = {}
user[KEY.ID] = event_info[KEY.LAUNCHER_ID]
user = get_user_information(user)
if user is not None:
event_info[KEY.LAUNCHER] = user[KEY.NICKNAME]
except:
pass
finally:
return event_info
def get_user_information(data):
if KEY.ID not in data:
if KEY.PHONE not in data:
return None
else:
sql = "select * from user where phone = %s"%(data[KEY.PHONE])
else:
sql = "select * from user where id = %d"%(data[KEY.ID])
try:
res = dbhelper.execute_fetchone(sql)
if res is None:
return None
else:
user = {}
user[KEY.ID] = res[0]
user[KEY.NAME] = res[1]
user[KEY.NICKNAME] = res[2]
user[KEY.GENDER] = res[3]
user[KEY.AGE] = res[4]
user[KEY.PHONE] = res[5]
user[KEY.LOCATION] = res[6]
user[KEY.LONGITUDE] = float(res[7])
user[KEY.LATITUDE] = float(res[8])
user[KEY.OCCUPATION] = res[9]
user[KEY.REPUTATION] = float(res[10])
user[KEY.IDENTITY_ID] = res[12]
user[KEY.IS_VERIFY] = res[14]
return user
except:
return None
def get_event_information(data):
if KEY.EVENT_ID not in data:
return None
event_info = None
sql = "select * from event where id = %d"
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID]))
if sql_result is not None:
event_info = {}
event_info[KEY.EVENT_ID] = sql_result[0]
event_info[KEY.LAUNCHER_ID] = sql_result[1]
event_info[KEY.TITLE] = sql_result[2]
event_info[KEY.CONTENT] = sql_result[3]
event_info[KEY.TYPE] = sql_result[4]
event_info[KEY.TIME] = str(sql_result[5])
event_info[KEY.LAST_TIME] = str(sql_result[6])
event_info[KEY.LONGITUDE] = float(sql_result[7])
event_info[KEY.LATITUDE] = float(sql_result[8])
event_info[KEY.STATE] = sql_result[9]
event_info[KEY.FOLLOW_NUMBER] = sql_result[10]
event_info[KEY.SUPPORT_NUMBER] = sql_result[11]
event_info[KEY.GROUP_PTS] = float(sql_result[12])
event_info[KEY.DEMAND_NUMBER] = sql_result[13]
event_info[KEY.LOVE_COIN] = sql_result[14]
event_info[KEY.COMMENT] = sql_result[15]
event_info[KEY.LOCATION] = sql_result[16]
user = {}
user[KEY.ID] = event_info[KEY.LAUNCHER_ID]
user = get_user_information(user)
if user is not None:
event_info[KEY.LAUNCHER] = user[KEY.NICKNAME]
except:
pass
finally:
return event_info
def user_event_manage(data):
if KEY.ID not in data or KEY.EVENT_ID not in data:
return False
if KEY.OPERATION not in data:
return True
if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2:
return False
sql = "select launcher from event where id = %d"
launcher_id = None
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID]))
if sql_result is not None:
launcher_id = sql_result[0]
except:
pass
if launcher_id is None:
return False
if data[KEY.OPERATION] == 0:
sql = "delete from support_relation where event_id = %d and supporter = %d"%(data[KEY.EVENT_ID], data[KEY.ID])
else:
sql = "replace into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())"%(data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION])
try:
dbhelper.execute(sql)
except:
return False
#
# trust and reputation compute here.
#
return True
def get_event_information(data):
if KEY.EVENT_ID not in data:
return None
event_info = None
sql = "select * from event where id = %d"
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID]))
if data[KEY.EVENT_ID] == 526:
print "search for EVENT_ID = 526"
if sql_result is not None:
event_info = {}
event_info[KEY.EVENT_ID] = sql_result[0]
event_info[KEY.LAUNCHER_ID] = sql_result[1]
event_info[KEY.CONTENT] = sql_result[2]
event_info[KEY.TYPE] = sql_result[3]
event_info[KEY.TIME] = str(sql_result[4])
event_info[KEY.LONGITUDE] = float(sql_result[5])
event_info[KEY.LATITUDE] = float(sql_result[6])
event_info[KEY.STATE] = sql_result[7]
event_info[KEY.FOLLOW_NUMBER] = sql_result[8]
event_info[KEY.SUPPORT_NUMBER] = sql_result[9]
event_info[KEY.GROUP_PTS] = float(sql_result[10])
event_info[KEY.MAX_PEOPLE] = sql_result[11]
user = {}
user[KEY.ID] = event_info[KEY.LAUNCHER_ID]
user = get_user_information(user)
if user is not None:
event_info[KEY.LAUNCHER] = user[KEY.NICKNAME]
except:
pass
finally:
return event_info
def user_event_manage(data):
if KEY.ID not in data or KEY.EVENT_ID not in data:
return False
if KEY.OPERATION not in data:
return True
if data[KEY.OPERATION] < 0 or data[KEY.OPERATION] > 2:
return False
sql = "select launcher from event where id = %d"
launcher_id = None
try:
sql_result = dbhelper.execute_fetchone(sql % (data[KEY.EVENT_ID]))
if sql_result is not None:
launcher_id = sql_result[0]
except:
pass
if launcher_id is None:
return False
if data[KEY.OPERATION] == 0:
sql = "delete from support_relation where event_id = %d and supporter = %d" % (
data[KEY.EVENT_ID], data[KEY.ID])
else:
sql = "replace into support_relation (event_id, supportee, supporter, type, time) values (%d, %d, %d, %d, now())" % (
data[KEY.EVENT_ID], launcher_id, data[KEY.ID], data[KEY.OPERATION])
try:
dbhelper.execute(sql)
except:
return False
#
# trust and reputation compute here.
#
return True
def calculate_number_of_login():
sql = "select count(*) from sign_in where to_days(time) = to_days(now())"
try:
result = dbhelper.execute_fetchone(sql)[0]
return result
except:
return -1
def calculate_number_of_user():
sql = "select count(*) from user"
try:
result = dbhelper.execute_fetchone(sql)[0]
return result
except:
return -1
def get_comment_info(data):
if KEY.COMMENT_ID not in data:
return None
sql = "select event_id, author, content, time, parent_author from comment where id = %d"
comment_info = None
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.COMMENT_ID]))
if sql_result is not None:
comment_info = {}
comment_info[KEY.COMMENT_ID] = data[KEY.COMMENT_ID]
comment_info[KEY.EVENT_ID] = sql_result[0]
comment_info[KEY.AUTHOR_ID] = sql_result[1]
comment_info[KEY.CONTENT] = sql_result[2]
comment_info[KEY.TIME] = str(sql_result[3])
comment_info[KEY.PARENT_AUTHOR_ID] = sql_result[4]
comment_info[KEY.AUTHOR] = None
comment_info[KEY.PARENT_AUTHOR] = None
user = {}
user[KEY.ID] = comment_info[KEY.AUTHOR_ID]
user = get_user_information(user)
if user is not None:
comment_info[KEY.AUTHOR] = user[KEY.NICKNAME]
user = {}
user[KEY.ID] = comment_info[KEY.PARENT_AUTHOR_ID]
user = get_user_information(user)
if user is not None:
comment_info[KEY.PARENT_AUTHOR] = user[KEY.NICKNAME]
except:
pass
finally:
return comment_info
def get_user_information(data):
if KEY.ID not in data:
return None
sql = "select * from user where id = %d"
try:
res = dbhelper.execute_fetchone(sql % (data[KEY.ID]))
if res is None:
return None
else:
user = {}
user[KEY.ID] = res[0]
user[KEY.NAME] = res[1]
user[KEY.NICKNAME] = res[2]
user[KEY.GENDER] = res[3]
user[KEY.AGE] = res[4]
user[KEY.PHONE] = res[5]
user[KEY.LOCATION] = res[6]
user[KEY.LONGITUDE] = float(res[7])
user[KEY.LATITUDE] = float(res[8])
user[KEY.OCCUPATION] = res[9]
user[KEY.REPUTATION] = float(res[10])
user[KEY.IDENTITY_ID] = res[12]
user[KEY.IS_VERIFY] = res[14]
return user
except:
return None
def get_supporter_num_from_event(eventId):
sql = "select count(*) from support_relation where event_id = %d"
try:
result = dbhelper.execute_fetchone(sql % (eventId))[0]
return result
except:
return None
def get_love_coin(data):
if KEY.ID not in data:
return -1
sql = "select coin from loving_bank where user_id = %d"%data[KEY.ID]
try:
result = dbhelper.execute_fetchone(sql)
return result[0]
except:
return -1
def get_user_id(data):
if KEY.USER_ACCOUNT not in data:
return -1
sql = "select id from account where account = %d"%data[KEY.USER_ACCOUNT]
sql_result = dbhelper.execute_fetchone(sql)
if sql_result is not None:
return sql_result[0]
else:
return -1
def get_coin_from_event(eventId):
sql = "select love_coin from event where id = %d"
try:
result = dbhelper.execute_fetchone(sql % (eventId))[0]
return result
except:
return None
def get_db_avatar(data):
if KEY.ID not in data:
return ""
sql = "select avatar from user where id = %d"
try:
result = dbhelper.execute_fetchone(sql%(data[KEY.ID]))
print "From database - get db avatar: user id - '%d', avatar - '%s'"%(data[KEY.ID], result[0])
return result[0]
except:
return ""
def upload_health(data):
if KEY.ID not in data:
return False
sql = "select * from health where user_id = %d"%data[KEY.ID]
res = dbhelper.execute_fetchone(sql)
if res is not None:
return update_health_record(data)
else:
if health_record(data) > 0:
return True
return False
def get_salt(data):
if KEY.ACCOUNT not in data:
return None
sql = "select salt from account where account = '%s'"
try:
res = dbhelper.execute_fetchone(sql%(data[KEY.ACCOUNT]))
if res is None:
return None
else:
return res[0]
except:
return None
def get_user_evaluate(data):
if KEY.ID not in data or KEY.EVENT_ID not in data:
return None
evaluate = {}
sql = "select value, comment from evaluation where event_id = %d and `to` = %d"
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.EVENT_ID], data[KEY.ID]))
if sql_result is not None:
evaluate[KEY.VALUE] = float(sql_result[0])
evaluate[KEY.COMMENT] = sql_result[1]
return evaluate
else:
return None
def get_salt(data):
if KEY.ACCOUNT not in data:
return None
sql = "select salt from account where account = '%s'"
try:
res = dbhelper.execute_fetchone(sql % (data[KEY.ACCOUNT]))
if res is None:
return None
else:
return res[0]
except:
return None
def is_sign_in(user_id):
result = False
sql = "select count(*) from sign_in where user_id = %d and to_days(time) = to_days(now())"
try:
sql_result = dbhelper.execute_fetchone(sql % (user_id))[0]
if sql_result > 0:
result = True
else:
result = False
except:
result = False
finally:
return result
def get_push_token(data):
if data == []:
return None
sql = "select push_token from account where id = %d"
token_list = []
for user_id in data:
token_result = dbhelper.execute_fetchone(sql%(user_id))
if token_result is not None:
token_list.append(token_result[0])
print "From database - get push token function: "
print token_list
return token_list
def is_sign_in(user_id):
result = False
sql = "select count(*) from sign_in where user_id = %d and to_days(time) = to_days(now())"
try:
sql_result = dbhelper.execute_fetchone(sql%(user_id))[0]
if sql_result > 0:
result = True
else:
result = False
except:
result = False
finally:
return result
def remove_static_relation(data):
if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.TYPE not in data:
return False
find_user_id = "select id from account where account = %d"
user_id = dbhelper.execute_fetchone(find_user_id%data[KEY.USER_ACCOUNT])
sql = "delete from static_relation where user_a = %d and user_b = %d and type = %d"
try:
n = dbhelper.execute(sql%(data[KEY.ID], user_id[0], data[KEY.TYPE]))
if n > 0:
return True
else:
return False
except:
return False
def get_events(data, get_event_id_list):
event_id_list = get_event_id_list(data)
event_list = []
sql = "select nickname from user where id = %d"
for event_id in event_id_list:
event_info = {}
event_info[KEY.EVENT_ID] = event_id
event_info = get_event_information(event_info)
if event_info is not None:
# find nickname of id
launcher_nickname = dbhelper.execute_fetchone(sql%(event_info[KEY.LAUNCHER_ID]))
event_info[KEY.LAUNCHER] = launcher_nickname[0]
event_list.append(event_info)
return event_list
def add_static_relation(data):
if KEY.ID not in data or KEY.USER_ACCOUNT not in data or KEY.TYPE not in data:
return False
find_user_id = "select id from account where account = %d"
user_id = dbhelper.execute_fetchone(find_user_id%data[KEY.USER_ACCOUNT])
#sql = "replace into static_relation (user_a, user_b, type, time) values (%d, %d, %d, now())"
sql = "insert into static_relation (user_a, user_b, type, time) values (%d, %d, %d, now())"
try:
n = dbhelper.execute(sql%(data[KEY.ID], user_id[0], data[KEY.TYPE]))
if n > 0:
return True
else:
return False
except:
return False
def get_friend(data):
if KEY.HOST_NAME not in data:
return None
event_info = None
sql = "select host_name from event where guest_name = %s and host_name = %s"
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.GUEST_NAME],data[KEY.HOST_NAME]))
if sql_result is not None:
friend_info = {}
friend_info[KEY.HOST_NAME] = sql_result[0]
friend_info[KEY.GUEST_NAME] = sql_result[1]
except:
pass
finally:
return friend_info
def get_illness_record(record_id):
sql = "select id, user_id, content, time from illness where id = %d"
record = None
try:
sql_result = dbhelper.execute_fetchone(sql%(record_id))
if sql_result is not None:
record = {}
record[KEY.ILLNESS_ID] = sql_result[0]
record[KEY.USER_ID] = sql_result[1]
record[KEY.CONTENT] = sql_result[2]
record[KEY.TIME] = str(sql_result[3])
except:
record = None
finally:
return record
def get_illness_record(record_id):
sql = "select id, user_id, content, time from illness where id = %d"
record = None
try:
sql_result = dbhelper.execute_fetchone(sql % (record_id))
if sql_result is not None:
record = {}
record[KEY.ILLNESS_ID] = sql_result[0]
record[KEY.USER_ID] = sql_result[1]
record[KEY.CONTENT] = sql_result[2]
record[KEY.TIME] = str(sql_result[3])
except:
record = None
finally:
return record
def get_health_record(record_id):
sql = "select id, user_id, type, value, time from health where id = %d"
record = None
try:
sql_result = dbhelper.execute_fetchone(sql % (record_id))
if sql_result is not None:
record = {}
record[KEY.HEALTH_ID] = sql_result[0]
record[KEY.USER_ID] = sql_result[1]
record[KEY.TYPE] = sql_result[2]
record[KEY.VALUE] = float(sql_result[3])
record[KEY.TIME] = str(sql_result[4])
except:
record = None
finally:
return record
def get_health_record(record_id):
sql = "select id, user_id, type, value, time from health where id = %d"
record = None
try:
sql_result = dbhelper.execute_fetchone(sql % (record_id))
if sql_result is not None:
record = {}
record[KEY.HEALTH_ID] = sql_result[0]
record[KEY.USER_ID] = sql_result[1]
record[KEY.TYPE] = sql_result[2]
record[KEY.VALUE] = float(sql_result[3])
record[KEY.TIME] = str(sql_result[4])
except:
record = None
finally:
return record
def get_user_loving_bank(data):
if KEY.USER_ID not in data:
return None
sql = "select score_rank, love_coin from loving_bank where userid = %d"
try:
res = dbhelper.execute_fetchone(sql%(data[KEY.USER_ID]))
if res is None:
return None
else:
bank_info = {}
bank_info[KEY.ID] = data[KEY.USER_ID]
bank_info[KEY.SCORE] = res[0]
bank_info[KEY.LOVE_COIN] = res[1]
return bank_info
except:
return None
def get_user_current_location(data):
location = {}
if KEY.ID not in data:
return location
sql = "select longitude, latitude from user where id = %d"%(data[KEY.ID])
try:
location_result = dbhelper.execute_fetchone(sql)
location[KEY.LONGITUDE] = location_result[0]
location[KEY.LATITUDE] = location_result[1]
print "From database - get user current location:"
print location
return location
except:
return location
def get_sign():
if KEY.ID not in data:
return None
sql = "select * from sign where sign_name = %s"
try:
res = dbhelper.execute_fetchone(sql%(data[KEY.BANK_NAME]))
if res is None:
return None
else:
user = {}
user[KEY.SIGN_ID] = res[0]
user[KEY.SIGN_NAME] = res[1]
user[KEY.SIGN_HISTORY] = res[2]
user[KEY.SIGN_TIME] = res[3]
return user
except:
return None
def get_bank(data):
if KEY.ID not in data:
return None
sql = "select * from bank where bank_name = %s"
try:
res = dbhelper.execute_fetchone(sql%(data[KEY.BANK_NAME]))
if res is None:
return None
else:
user = {}
user[KEY.BANK_ID] = res[0]
user[KEY.BANK_NAME] = res[1]
user[KEY.GRADE] = res[2]
user[KEY.COIN] = res[3]
return user
except:
return None
def get_question_information(data):
if KEY.QUESTION_ID not in data:
return None
event_info = None
sql = "select question_id from question where question_time = %s"
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.QUESTION_TIME]))
if sql_result is not None:
question_info = {}
question_info[KEY.QUESTION_ID] = sql_result[0]
question_info[QUESTION_CONTENT] = sql_result[1]
question_info[QUESTION_TYPE] = sql_result[2]
question_info[QUESTION_TIME] = sql_result[3]
except:
pass
finally:
return question_info
def get_answer(data):
if KEY.ANSWER_ID not in data:
return None
event_info = None
sql = "select answer_id from answer where question_id = %s"
try:
sql_result = dbhelper.execute_fetchone(sql%(data[KEY.QUESTION_ID]))
if sql_result is not None:
question_info = {}
question_info[KEY.ANSWER_ID] = sql_result[0]
question_info[ANSWER_CONTENT] = sql_result[1]
question_info[ANSWER_TYPE] = sql_result[2]
question_info[ANSWER_TIME] = sql_result[3]
except:
pass
finally:
return question_info
def get_all_event_list(data):
event_id_list = []
sql = "select id from event where id = %d"
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"
for event_id in data[KEY.EVENT_LIST]:
result = dbhelper.execute_fetchone(sql%event_id)
if result is not None:
event_id_list.append(result[0])
event_id_list = event_id_list[::-1]
print "From database - get all events function: id list:"
print event_id_list
return event_id_list
def validate_password(data):
if KEY.ACCOUNT not in data or KEY.PASSWORD not in data or KEY.SALT not in data:
return -1
sql = "select id, password from account where account = '%s' and salt = '%s'"
user_id = -1
password = None
try:
res = dbhelper.execute_fetchone(sql %
(data[KEY.ACCOUNT], data[KEY.SALT]))
if res is not None:
user_id = res[0]
password = res[1]
except:
pass
finally:
if password is None or data[KEY.PASSWORD] is None:
return -1
elif password == data[KEY.PASSWORD]:
return user_id
else:
return -1
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))
def get_comment_info(data):
if KEY.COMMENT_ID not in data:
return None
sql = "select event_id, author, content, time from comment where id = %d"
comment_info = None
try:
sql_result = dbhelper.execute_fetchone(sql % (data[KEY.COMMENT_ID]))
if sql_result is not None:
comment_info = {}
comment_info[KEY.COMMENT_ID] = data[KEY.COMMENT_ID]
comment_info[KEY.EVENT_ID] = sql_result[0]
comment_info[KEY.AUTHOR_ID] = sql_result[1]
comment_info[KEY.CONTENT] = sql_result[2]
comment_info[KEY.TIME] = str(sql_result[3])
user = {}
user[KEY.ID] = comment_info[KEY.AUTHOR_ID]
user = get_user_information(user)
if user is not None:
comment_info[KEY.AUTHOR] = user[KEY.NICKNAME]
except:
pass
finally:
return comment_info
