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)