def get_data(aoh, oh, db, cursor):
dict = []
sql = "SELECT * FROM fountain_default"
result = db_op.sql_execute(db, cursor, sql, False)
for re in result:
if re[4] in aoh:
dict.append({"config": {"position": {"lat": re[5], "lng": re[6]},
"title": "D" + str(re[0]).zfill(4)},
"info": {"id": "D" + str(re[0]).zfill(4),
"place": re[1],
"type": re[2],
"location": re[3],
"open": open_hour_2string(oh[re[4]]),
"number": re[7],
"status": re[8]}})
sql = "SELECT * FROM fountain_user"
result = db_op.sql_execute(db, cursor, sql, False)
for re in result:
if re[4] in aoh:
dict.append({"config": {"position": {"lat": re[5], "lng": re[6]},
"title": "U" + str(re[0]).zfill(4)},
"info": {"id": "U" + str(re[0]).zfill(4),
"place": re[1],
"type": re[2],
"location": re[3],
"open": open_hour_2string(oh[re[4]]),
"number": re[7],
"status": re[8]}})
return dict
def insert_user(usr, pwd, helper, db, cursor):
sql = "INSERT INTO users (username,password,help) VALUES ('" + usr + "',PASSWORD('" + \
pwd + "')," + (str(1) if helper else str(0)) + ")"
db_op.sql_execute(db, cursor, sql, True)
sql = "INSERT INTO user_setting () VALUES ()"
db_op.sql_execute(db, cursor, sql, True)
return
def report_data(info, uid, db, cursor):
open = find_open_type(info['open'], db, cursor)
sql = "INSERT INTO `fountain_user` " + \
"(`place`,`type`,`location`,`open`,`latitude`,`longitude`,`number`,`status`,`uid`) VALUES ('" + \
info['place'] + "'," + info['type'] + ",'" + info['location'] + "'," + open + "," + \
str(info['position']['lat']) + "," + str(info['position']['lng']) + "," + str(info['number']) + \
",0," + uid + ")"
db_op.sql_execute(db, cursor, sql, True)
return
def get_confirm_data(idx, db, cursor):
if idx[0] == 'D':
sql = 'SELECT status FROM fountain_default WHERE id =' + str(int(idx[1:]))
result = db_op.sql_execute(db, cursor, sql, False)
if len(result) != 1:
return None, None
return result[0][0], -1
else:
sql = 'SELECT status,uid FROM fountain_user WHERE id =' + str(int(idx[1:]))
result = db_op.sql_execute(db, cursor, sql, False)
if len(result) != 1:
return None, None
return result[0][0], result[0][1]
def get_open_hour(wd, db, cursor):
sql = "SELECT `type`,`from`,`to` FROM open_hour WHERE weekday=" + wd
result = db_op.sql_execute(db, cursor, sql, False)
dict = {}
for re in result:
if re[0] in dict:
dict.get(re[0]).append((re[1], re[2]))
else:
dict[re[0]] = [(re[1], re[2])]
return dict
def find_open_type(open, db, cursor):
set_lst = []
for key in open.keys():
sql = "SELECT `type` FROM open_hour WHERE weekday=" + key + " AND `from`='" + open[key][0] + "' AND `to`='" + \
open[key][1] + "'"
result = db_op.sql_execute(db, cursor, sql, False)
tmp = []
for re in result:
tmp.append(re[0])
set_lst.append(set(tmp))
op = set.intersection(*set_lst)
if len(op) == 0:
sql = 'SELECT MAX(`type`) FROM open_hour'
result = db_op.sql_execute(db, cursor, sql, False)
type = result[0][0] + 1
for key in open.keys():
sql = "INSERT INTO `open_hour` (`type`,`weekday`,`from`,`to`) VALUES (" + str(type) + "," + key + ",'" + \
open[key][0] + "','" + open[key][1] + "')"
db_op.sql_execute(db, cursor, sql, True)
return str(type)
else:
return str(list(op)[0])
def confirm_data(idx, ava, db, cursor):
table = 'fountain_default' if idx[0] == 'D' else 'fountain_user'
sql = 'UPDATE ' + table + ' SET status=' + ava + ' WHERE id=' + str(int(idx[1:]))
db_op.sql_execute(db, cursor, sql, True)
return
def auth_user(id, tkn, db, cursor):
sql = "SELECT COUNT(*) FROM users WHERE id=" + id + " AND token='" + tkn + "'"
result = db_op.sql_execute(db, cursor, sql, False)
if result[0][0] != 1:
return False
return True
def select_user_with_account(usr, pwd, db, cursor):
sql = "SELECT id, help FROM users WHERE username='******' AND password=Password('" + pwd + "')"
result = db_op.sql_execute(db, cursor, sql, False)
if len(result) != 1:
return None, None
return result[0][0], (result[0][1] == 1)
def check_account(usr, db, cursor):
sql = "SELECT COUNT(*) FROM users WHERE username='******'"
result = db_op.sql_execute(db, cursor, sql, False)
if result[0][0] != 0:
return False
return True
def clear_token(id, db, cursor):
sql = "UPDATE users SET token='', tokenExpire=TIMESTAMP(0) WHERE id=" + id
db_op.sql_execute(db, cursor, sql, True)
return
def update_token(id, tkn, db, cursor):
sql = "UPDATE users SET token='" + tkn + "', tokenExpire=DATE_ADD(NOW(), INTERVAL 7 DAY) WHERE id=" + id
db_op.sql_execute(db, cursor, sql, True)
return
def update_setting(id, vt, vs, vr, db, cursor):
sql = "UPDATE user_setting SET view_type='" + vt + "', view_status='" + vs + "', view_range=" + \
vr + " WHERE id=" + id
db_op.sql_execute(db, cursor, sql, True)
return
def select_user_setting(id, db, cursor):
sql = "SELECT * FROM user_setting WHERE id=" + id
result = db_op.sql_execute(db, cursor, sql, False)
if len(result) != 1:
return None, None, None
return result[0][1], result[0][2], result[0][3]
def select_user_with_token(tkn, db, cursor):
sql = "SELECT id, help FROM users WHERE token='" + tkn + "' AND tokenExpire>=NOW()"
result = db_op.sql_execute(db, cursor, sql, False)
if len(result) != 1:
return None, None
return result[0][0], (result[0][1] == 1)
