def createUser():
logging.info("================USER CREATION")
try:
email = request.json["email"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
name = request.json["name"]
except:
name = None
logging.info("NAME IS None")
logging.info("_____________________")
try:
username = request.json["username"]
except:
username = None
logging.info("USERNAME IS None")
logging.info("_____________________")
try:
about = request.json["about"]
except:
about = None
logging.info("ABOUT IS None")
logging.info("_____________________")
if (not isString([name, email, username, about])):
logging.info("one or more params not string")
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idUser FROM User WHERE email = %s"
cursor.execute(sql, [email])
if (cursor.fetchone() != None):
logging.info(email + " is already exists")
return json.dumps({"code": 5, "response": error_messages[5]})
isAnonymous = False
if ("isAnonymous" in request.json):
isAnonymous = request.json["isAnonymous"]
sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, [username, email, name, about, isAnonymous])
sql = "SELECT MAX(idUser) FROM User"
cursor.execute(sql)
idU = cursor.fetchone()[0]
data = {}
data['about'] = about
data['email'] = email
data['id'] = idU
data['isAnonymous'] = isAnonymous
data['name'] = name
data['username'] = username
answer = {"code": 0, "response": data}
response = json.dumps(answer)
logging.info("================SUCCESSFUL USER CREATION\n")
return response
def status():
db_info = { "forum": 0, "user": 0, "thread": 0, "post": 0 }
cursor.execute("SELECT COUNT(*) FROM Forum")
db_info["forum"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM User ")
db_info["user"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM Thread")
db_info["thread"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM Post")
db_info["post"] = cursor.fetchone()[0]
response = json.dumps({"code": 0, "response": db_info})
return response
def closeThread():
tic = time()
# logging.info("=====================================CLOSING THREAD BEGIN==========================================")
try:
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
# logging.info(" thread : " + str(thread))
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
data = cursor.fetchone()
if (not data):
# logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n")
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Thread SET isClosed = 1 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({"code": 0, "response": thread})
# logging.info(" Response : ")
# logging.info(response)
# logging.info("=====================================CLOSING THREAD END============================================\n")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/close/ +++POST")
return response
def getArrayThreadsFromDDictionary(dictionary, related):
array = []
for item in dictionary:
try:
threadID = item[0]
sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0"
cursor.execute(sql, [threadID])
count_posts = cursor.fetchone()[0]
logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts))
except:
count_posts = 0
answer = {}
answer["id"] = item[0]
answer["title"] = item[1]
answer["message"] = item[2]
answer["slug"] = item[3]
answer["date"] = str(item[4])
answer["isClosed"] = item[5]
answer["isDeleted"] = item[6]
answer["forum"] = getForumShortNameById(item[7])
answer["user"] = getUserEmailByID(item[8])
answer["likes"] = item[9]
answer["dislikes"] = item[10]
answer["posts"] = count_posts
answer["points"] = answer["likes"] - answer["dislikes"]
if "user" in related:
answer["user"] = getUserInfoByEmail(answer["user"])
if "forum" in related:
answer["forum"] = getForumDetailsByShortName(answer["forum"])
array.append(answer)
return array
def restoreThread():
tic = time()
from Post import restorePostsOfThread
if "thread" in request.json:
# logging.info("RESTORING THREAD")
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
if cursor.fetchone() is None:
return json.dumps({"code": 1, "response": error_messages[1]})
restorePostsOfThread(thread)
sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({ "code": 0, "response": {"thread": thread}})
# logging.info("REMOVING THREAD SUCCESSFULL\n")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/restore/ +++POST")
return response
def updatePost():
tic = time()
#logging.info(" Updating post")
if "post" in request.json and "message" in request.json:
post = request.json["post"]
message = request.json["message"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result_arr = cursor.fetchone()
result = result_arr[0]
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET message = %s WHERE idPost = %s"
cursor.execute(sql, [message, post])
response = json.dumps({"code": 0, "response": post})
# logging.info(" Post " + str(post) + (" is updated successfully\n"))
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/post/update/ +++POST")
return response
def getUserIdByEmail(email):
logging.info(" getting user by email : ")
sql = "SELECT idUser FROM User WHERE email = %s"
cursor.execute(sql, [email])
q_result = cursor.fetchone()[0]
logging.info(" getted user by email : " + str(q_result))
return q_result
def getForumIdByShortName(short_name):
sql = "SELECT idForum FROM Forum WHERE short_name = %s"
cursor.execute(sql, [short_name])
data = cursor.fetchone()
if (not data):
return None
answer = data[0]
return answer
def getForumShortNameById(id):
sql = "SELECT short_name FROM Forum WHERE idForum = %s"
cursor.execute(sql, [id])
data = cursor.fetchone()
if (not data):
return None
answer = data[0]
return answer
def status():
tic = time()
db_info = { "forum": 0, "user": 0, "thread": 0, "post": 0 }
cursor.execute("SELECT COUNT(*) FROM Forum")
db_info["forum"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM User ")
db_info["user"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM Thread")
db_info["thread"] = cursor.fetchone()[0]
cursor.execute("SELECT COUNT(*) FROM Post")
db_info["post"] = cursor.fetchone()[0]
response = json.dumps({"code": 0, "response": db_info})
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/status/")
return response
def createForum():
tic = time()
#logging.info("================FORUM CREATION")
# logging.info("REQUEST :")
# logging.info(request.json)
# logging.info("SH_NAME : " + request.json["short_name"])
# logging.info("USER : "******"user"])
# logging.info("NAME : " + request.json["name"].encode("UTF-8"))
try:
name = request.json["name"].encode("UTF-8")
#logging.info("NAME : " + name)
short_name = request.json["short_name"]
#logging.info("SHORT_NAME : " + short_name)
user = request.json["user"]
#logging.info("USER : "******"error in parsing params")
return json.dumps({"code": 2, "response": error_messages[2]})
cursor.execute("SELECT idUser FROM User WHERE User.email = %s", [user])
id_User = cursor.fetchone()
if (not id_User):
return json.dumps({"code": 1, "response": error_messages[1]})
id_User = id_User[0]
sql = "SELECT * FROM Forum WHERE name = %s"
cursor.execute(sql, [name])
if (cursor.fetchone()):
return json.dumps({"code": 5, "response": error_messages[5]})
sql = "INSERT INTO Forum (name, short_name, idFounder) VALUES (%s, %s, %s)"
cursor.execute(sql, [name, short_name, id_User])
sql = "SELECT max(idForum) FROM Forum"
cursor.execute(sql)
idF = cursor.fetchone()[0]
answer = {"code": 0, "response": {"id": idF, "name": name, "short_name":short_name, "user": user}}
response = json.dumps(answer)
#logging.info("================SUCCESSFUL FORUM CREATION\n")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "db/api/forum/create/")
return response
def getUserEmailByID(id):
sql = "SELECT email FROM User WHERE idUser = %s"
cursor.execute(sql, [id])
q_result = cursor.fetchone()
if (q_result != None):
return q_result[0]
else:
return None
def getUserEmailByID(id):
sql = "SELECT email FROM User WHERE idUser = %s"
cursor.execute(sql, [id])
q_result = cursor.fetchone()
if (q_result != None):
return q_result[0]
else:
return None
def get(user_id):
if not user_id:
return None
cursor.execute("SELECT * FROM users WHERE id = " + str(user_id))
res = cursor.fetchone()
if res:
user = User(res)
return user
else:
return None
def createThread():
tic = time()
#logging.info("================Thread CREATION")
try:
forum = request.json["forum"]
title = request.json["title"]
isClosed = request.json["isClosed"]
date = request.json["date"]
message = request.json["message"]
slug = request.json["slug"]
user = request.json["user"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
isDeleted = request.json["isDeleted"]
except:
isDeleted = False
try:
id_Forum = getForumDetailsByShortName(forum)["id"]
id_User = getUserInfoByEmail(user)["id"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "INSERT INTO Thread(title, message, slug, date, isClosed, isDeleted, idForum, idAuthor, likes, dislikes) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, [title, message, slug, date, isClosed, isDeleted, id_Forum, id_User, 0 , 0])
sql = "SELECT MAX(idThread) FROM Thread"
cursor.execute(sql)
idTh = cursor.fetchone()[0]
answer = {}
answer["date"] = date
answer["forum"] = forum
answer["id"] = idTh
answer["isClosed"] = isClosed
answer["isDeleted"] = isDeleted
answer["likes"] = 0
answer["dislikes"] = 0
answer["message"] = message
answer["points"] = answer["likes"] - answer["dislikes"]
answer["posts"] = 0
answer["slug"] = slug
answer["title"] = title
answer["user"] = user
response = json.dumps({"code": 0, "response": answer })
# logging.info(" Answer : " + response)
#logging.info("================SUCCESSFUL THREAD CREATION\n")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/create/ +++POST")
return response
def getForumDetailsById(id):
sql = "SELECT * FROM Forum WHERE idForum = %s"
cursor.execute(sql, [id])
data = cursor.fetchone()
if (not data):
return None
answer = {}
answer["id"] = data[0]
answer["name"] = data[1]
answer["short_name"] = data[2]
answer["idFounder"] = data[3]
return answer
def createThread():
logging.info("================Thread CREATION")
try:
forum = request.json["forum"]
title = request.json["title"]
isClosed = request.json["isClosed"]
date = request.json["date"]
message = request.json["message"]
slug = request.json["slug"]
user = request.json["user"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
isDeleted = request.json["isDeleted"]
except:
isDeleted = False
try:
id_Forum = getForumDetailsByShortName(forum)["id"]
id_User = getUserInfoByEmail(user)["id"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "INSERT INTO Thread(title, message, slug, date, isClosed, isDeleted, idForum, idAuthor, likes, dislikes) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, [
title, message, slug, date, isClosed, isDeleted, id_Forum, id_User, 0,
0
])
sql = "SELECT MAX(idThread) FROM Thread"
cursor.execute(sql)
idTh = cursor.fetchone()[0]
answer = {}
answer["date"] = date
answer["forum"] = forum
answer["id"] = idTh
answer["isClosed"] = isClosed
answer["isDeleted"] = isDeleted
answer["likes"] = 0
answer["dislikes"] = 0
answer["message"] = message
answer["points"] = answer["likes"] - answer["dislikes"]
answer["posts"] = 0
answer["slug"] = slug
answer["title"] = title
answer["user"] = user
response = json.dumps({"code": 0, "response": answer})
logging.info(" Answer : " + response)
logging.info("================SUCCESSFUL THREAD CREATION\n")
return response
def getForumDetailsById(id):
sql = "SELECT * FROM Forum WHERE idForum = %s"
cursor.execute(sql, [id])
data = cursor.fetchone()
if (not data):
return None
answer = {}
answer["id"] = data[0]
answer["name"] = data[1]
answer["short_name"] = data[2]
answer["idFounder"] = data[3]
return answer
def createForum():
logging.info("================FORUM CREATION")
# logging.info("REQUEST :")
# logging.info(request.json)
# logging.info("SH_NAME : " + request.json["short_name"])
# logging.info("USER : "******"user"])
# logging.info("NAME : " + request.json["name"].encode("UTF-8"))
try:
name = request.json["name"].encode("UTF-8")
logging.info("NAME : " + name)
short_name = request.json["short_name"]
logging.info("SHORT_NAME : " + short_name)
user = request.json["user"]
logging.info("USER : "******"error in parsing params")
return json.dumps({"code": 2, "response": error_messages[2]})
cursor.execute("SELECT idUser FROM User WHERE User.email = %s", [user])
id_User = cursor.fetchone()
if (not id_User):
return json.dumps({"code": 1, "response": error_messages[1]})
id_User = id_User[0]
sql = "SELECT * FROM Forum WHERE name = %s"
cursor.execute(sql, [name])
if (cursor.fetchone()):
return json.dumps({"code": 5, "response": error_messages[5]})
sql = "INSERT INTO Forum (name, short_name, idFounder) VALUES (%s, %s, %s)"
cursor.execute(sql, [name, short_name, id_User])
sql = "SELECT max(idForum) FROM Forum"
cursor.execute(sql)
idF = cursor.fetchone()[0]
answer = {"code": 0, "response": {"id": idF, "name": name, "short_name":short_name, "user": user}}
response = json.dumps(answer)
logging.info("================SUCCESSFUL FORUM CREATION\n")
return response
def map2():
if request.method == "POST":
req = request.form
scoords = req['scoords']
ecoords = req['ecoords']
cursor.execute(
"""SELECT * FROM `users` WHERE `email` LIKE '{}' """.format(
email))
user = cursor.fetchone()
pooling = 1
cursor.execute(
"""UPDATE `users` set pooling='{}' WHERE email='{}' """.format(
pooling, email))
cursor.execute(
"""UPDATE `users` set starting_coords='{}' WHERE email='{}' """.
format(scoords, email))
cursor.execute(
"""UPDATE `users` set ending_coords='{}' WHERE email='{}' """.
format(ecoords, email))
conn.commit()
cursor.execute(
"""SELECT * FROM `users` WHERE `email` LIKE '{}' """.format(email))
user = cursor.fetchone()
cursor.execute("""SELECT * FROM `users` """)
records = cursor.fetchall()
u1 = (user[1], user[7])
u2 = (user[1], user[8])
d1 = {record[1]: record[7] for record in records}
d2 = {record[1]: record[8] for record in records}
# User's match based on compatibility types
max_user = matching.match(u2, d2)
cursor.execute(
"""SELECT * FROM `users` WHERE `name` LIKE '{}' """.format(max_user))
usr_phn = cursor.fetchone()
phn = usr_phn[4]
pooling = 0
cursor.execute(
"""UPDATE `users` set pooling='{}' WHERE email='{}' """.format(
pooling, email))
return render_template('map2.html', user=max_user, phn=phn)
def getThreadDetailsByID(threadID, related):
sql = "SELECT * FROM Thread WHERE idThread = %s"
cursor.execute(sql, [threadID])
data = cursor.fetchone()
if (not data):
# logging.info("Thread not found")
return None
try:
sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0"
cursor.execute(sql, [threadID])
count_posts = cursor.fetchone()[0]
# logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts))
except:
count_posts = 0
answer = {}
answer["id"] = data[0]
answer["title"] = data[1]
answer["message"] = data[2]
answer["slug"] = data[3]
answer["date"] = str(data[4])
answer["isClosed"] = data[5]
answer["isDeleted"] = data[6]
forum_details = getForumDetailsById(data[7])
answer["forum"] = forum_details["short_name"]
answer["user"] = getUserEmailByID(data[8])
answer["likes"] = data[9]
answer["dislikes"] = data[10]
answer["posts"] = count_posts
answer["points"] = answer["likes"] - answer["dislikes"]
if "user" in related:
data_user = getUserInfoByEmail(answer["user"])
answer["user"] = data_user
if "forum" in related:
data_forum = getForumDetailsByShortName(answer["forum"])
answer["forum"] = data_forum
# logging.info(" ===========Answer getThreadByID() : ")
#logging.info(answer)
# logging.info(" ===================================\n")
return answer
def getThreadDetailsByID(threadID, related):
sql = "SELECT * FROM Thread WHERE idThread = %s"
cursor.execute(sql, [threadID])
data = cursor.fetchone()
if (not data):
logging.info("Thread not found")
return None
try:
sql = "SELECT count(*) FROM Post WHERE idThread = %s AND isDeleted = 0"
cursor.execute(sql, [threadID])
count_posts = cursor.fetchone()[0]
logging.info(" Count posts of thread " + str(threadID) + " is " + str(count_posts))
except:
count_posts = 0
answer = {}
answer["id"] = data[0]
answer["title"] = data[1]
answer["message"] = data[2]
answer["slug"] = data[3]
answer["date"] = str(data[4])
answer["isClosed"] = data[5]
answer["isDeleted"] = data[6]
forum_details = getForumDetailsById(data[7])
answer["forum"] = forum_details["short_name"]
answer["user"] = getUserEmailByID(data[8])
answer["likes"] = data[9]
answer["dislikes"] = data[10]
answer["posts"] = count_posts
answer["points"] = answer["likes"] - answer["dislikes"]
if "user" in related:
data_user = getUserInfoByEmail(answer["user"])
answer["user"] = data_user
if "forum" in related:
data_forum = getForumDetailsByShortName(answer["forum"])
answer["forum"] = data_forum
logging.info(" ===========Answer getThreadByID() : ")
logging.info(answer)
logging.info(" ===================================\n")
return answer
def getForumDetailsByShortName(short_name):
from User import getUserEmailByID
sql = "SELECT * FROM Forum WHERE short_name = %s"
cursor.execute(sql, [short_name])
data = cursor.fetchone()
if (not data):
return None
answer = {}
answer["id"] = data[0]
answer["name"] = data[1]
answer["short_name"] = data[2]
answer["idFounder"] = data[3]
answer["user"] = getUserEmailByID(answer["idFounder"])
return answer
def getForumDetailsByShortName(short_name):
from User import getUserEmailByID
sql = "SELECT * FROM Forum WHERE short_name = %s"
cursor.execute(sql, [short_name])
data = cursor.fetchone()
if (not data):
return None
answer = {}
answer["id"] = data[0]
answer["name"] = data[1]
answer["short_name"] = data[2]
answer["idFounder"] = data[3]
answer["user"] = getUserEmailByID(answer["idFounder"])
return answer
def edit_product(data):
sql = "SELECT * FROM products WHERE product_id = %d" % data["product_id"]
print(sql)
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchone()
print(type(result[6]))
if result[6] == 2 or result[6] == 3 or result[6] == 6 or result[
6] == 7 or result[6] == 15:
sql = "UPDATE products SET product_name = '%s',product_description = '%s',product_price = '%8.2f', product_qty = '%d', created_by = '%d', actions = '%d' WHERE product_id = %d" % (
data["product_name"], data["product_description"],
data["product_price"], data["product_qty"], data["created_by"],
data["actions"], data["product_id"])
print(sql)
status = 0
res = ''
try:
cursor.execute(sql)
db.commit()
status = 200
res = 'Product updated successfully'
except pymysql.Error as e:
try:
print("MySQL Error" + str(e))
return None
except IndexError:
print("MySQL Error: %s") % str(e)
return None
except:
db.rollback()
status = 401
res = 'Something went wrong. Try again'
else:
status = 201
res = "Not authorized"
except:
print("Error: unable to fetch data")
return res, status
def removePost():
if "post" in request.json:
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result = cursor.fetchone()
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s"
cursor.execute(sql, [post])
response = json.dumps({"code": 0, "response": post})
return response
def removePost():
if "post" in request.json:
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result = cursor.fetchone()
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s"
cursor.execute(sql, [post])
response = json.dumps({"code": 0, "response": post})
return response
def check():
conn = mysql.connect()
cursor = conn.cursor()
print "ddd"
username = str(request.form["name"])
userpassword = str(request.form["password"])
cursor.execute("SELECT * FROM usertable WHERE username ='******' and userpassword = '******'")
username = cursor.fetchone()
id = username[0]
name = username[1]
password = username[2]
age = username[3]
gender = username[4]
permission = username[5]
status = username[6]
print(id)
print(name)
print(password)
print(age)
print(gender)
print(permission)
print(status)
#print(username)
if not username:
return render_template("index.html")
elif username and permission == "admin":
return render_template("adminpage.html")
elif username and permission == "user":
return render_template("index.html")
elif username and permission == "manager":
return render_template("adminpage.html")
cursor.execute('call add_loginvisitor(%s,%s)', (username, userpassword))
def form_data():
global result
if request.method == "POST":
req = request.form
movie = req['movie']
music = req['music']
sport = req['sport']
choice = req['choice']
result = movie + music + sport + choice
cursor.execute(
"""SELECT * FROM `users` WHERE `email` LIKE '{}' AND `password` LIKE '{}' """
.format(email, password))
user = cursor.fetchone()
cursor.execute(
"""UPDATE `users` set results='{}' WHERE email='{}' """.format(
result, email))
conn.commit()
conn.commit()
return render_template('home.html')
def openThread():
if "thread" in request.json:
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" thread : " + str(thread))
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
data = cursor.fetchone()
if not data:
logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n")
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Thread SET isClosed = 0 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({"code": 0, "response": thread})
return response
def getUserInfoByID(id):
sql = "SELECT * FROM User WHERE idUser = %s"
cursor.execute(sql, [id])
q_result = cursor.fetchone()
if (q_result != None):
data = {}
data["id"] = q_result[0]
data["username"] = q_result[1]
data["email"] = q_result[2]
data["name"] = q_result[3]
data["about"] = q_result[4]
data["isAnonymous"] = q_result[5]
data["followers"] = getFollowerEmails(data["id"], None, None, None)
data["following"] = getFollowingEmails(data["id"], None, None, None)
data["subscriptions"] = getSubscriptions(data["id"])
return data
else:
return None
def getUserInfoByID(id):
sql = "SELECT * FROM User WHERE idUser = %s"
cursor.execute(sql, [id])
q_result = cursor.fetchone()
if (q_result != None):
data = {}
data["id"] = q_result[0]
data["username"] = q_result[1]
data["email"] = q_result[2]
data["name"] = q_result[3]
data["about"] = q_result[4]
data["isAnonymous"] = q_result[5]
data["followers"] = getFollowerEmails(data["id"], None, None, None)
data["following"] = getFollowingEmails(data["id"], None, None, None)
data["subscriptions"] = getSubscriptions(data["id"])
return data
else:
return None
def updatePost():
logging.info(" Updating post")
if "post" in request.json and "message" in request.json:
post = request.json["post"]
message = request.json["message"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result_arr = cursor.fetchone()
result = result_arr[0]
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET message = %s WHERE idPost = %s"
cursor.execute(sql, [message, post])
response = json.dumps({"code": 0, "response": post})
logging.info(" Post " + str(post) + (" is updated successfully\n"))
return response
def updatePost():
logging.info(" Updating post")
if "post" in request.json and "message" in request.json:
post = request.json["post"]
message = request.json["message"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result_arr = cursor.fetchone()
result = result_arr[0]
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET message = %s WHERE idPost = %s"
cursor.execute(sql, [message, post])
response = json.dumps({"code": 0, "response": post})
logging.info(" Post " + str(post) + (" is updated successfully\n"))
return response
def openThread():
if "thread" in request.json:
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" thread : " + str(thread))
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
data = cursor.fetchone()
if not data:
logging.info(
"=====================================CLOSING THREAD END WITHOUT DATA===============================\n"
)
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Thread SET isClosed = 0 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({"code": 0, "response": thread})
return response
def removePost():
tic = time()
if "post" in request.json:
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result = cursor.fetchone()
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s"
cursor.execute(sql, [post])
response = json.dumps({"code": 0, "response": post})
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/post/remove/ +++POST")
return response
def removePost():
tic = time()
if "post" in request.json:
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idPost FROM Post WHERE idPost = %s"
cursor.execute(sql, [post])
result = cursor.fetchone()
if not result:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Post SET isDeleted = 1 WHERE idPost = %s"
cursor.execute(sql, [post])
response = json.dumps({"code": 0, "response": post})
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/post/remove/ +++POST")
return response
def get_product(product_id):
sql = "SELECT * FROM products WHERE product_id = %d" % product_id
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchone()
print(type(result))
data = {
'id': result[0],
'product_name': result[1],
'product_description': result[2],
'product_price': result[3],
'product_qty': result[4],
'created_by': result[5],
'actions': result[6]
}
status = 200
res = data
except:
print("Error: unable to fetch data")
return res, status
def closeThread():
logging.info("=====================================CLOSING THREAD BEGIN==========================================")
try:
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" thread : " + str(thread))
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
data = cursor.fetchone()
if (not data):
logging.info("=====================================CLOSING THREAD END WITHOUT DATA===============================\n")
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "UPDATE Thread SET isClosed = 1 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({"code": 0, "response": thread})
logging.info(" Response : ")
logging.info(response)
logging.info("=====================================CLOSING THREAD END============================================\n")
return response
def restoreThread():
from Post import restorePostsOfThread
if "thread" in request.json:
logging.info("RESTORING THREAD")
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
if cursor.fetchone() is None:
return json.dumps({"code": 1, "response": error_messages[1]})
restorePostsOfThread(thread)
sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({"code": 0, "response": {"thread": thread}})
logging.info("REMOVING THREAD SUCCESSFULL\n")
return response
def restoreThread():
from Post import restorePostsOfThread
if "thread" in request.json:
logging.info("RESTORING THREAD")
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idThread FROM Thread WHERE idThread = %s"
cursor.execute(sql, [thread])
if cursor.fetchone() is None:
return json.dumps({"code": 1, "response": error_messages[1]})
restorePostsOfThread(thread)
sql = "UPDATE Thread SET isDeleted = 0 WHERE idThread = %s"
cursor.execute(sql, [thread])
response = json.dumps({ "code": 0, "response": {"thread": thread}})
logging.info("REMOVING THREAD SUCCESSFULL\n")
return response
def work():
global users_dic_work
if not current_user.is_authenticated:
return redirect(url_for('login'))
if current_user.weapon_id is None:
flash("未装备武器！")
return redirect(url_for('index'))
else:
if users_dic_work[current_user.id] == 1:
cursor.execute("SELECT * FROM items WHERE id=%s" %
current_user.weapon_id)
res = cursor.fetchone()
weapon = Item(res)
gold = current_user.gold
cursor.execute("UPDATE users SET gold = %s WHERE id=%s" %
(gold + weapon.value, current_user.id))
conn.commit()
flash("获得" + str(weapon.value) + "金币！")
users_dic_work[current_user.id] = 0
return redirect(url_for('index'))
else:
flash("未到出击时间！")
return redirect(url_for('index'))
def getPostDetailsByID(postID, related):
sql = "SELECT * FROM Post WHERE idPost = %s"
cursor.execute(sql, [postID])
data = cursor.fetchone()
# logging.info(data)
if (not data):
#logging.info(" Thread not found")
return None
answer = {}
answer["id"] = data[0]
answer["parent"] = data[1]
answer["isApproved"] = data[2]
answer["isHighlighted"] = data[3]
answer["isEdited"] = data[4]
answer["isSpam"] = data[5]
answer["isDeleted"] = data[6]
answer["likes"] = data[7]
answer["dislikes"] = data[8]
answer["date"] = str(data[9])
answer["message"] = data[10]
answer["forum"] = getForumShortNameById(data[11])
answer["thread"] = data[12]
answer["user"] = getUserEmailByID(data[13])
answer["points"] = answer["likes"] - answer["dislikes"]
if "user" in related:
data_user = getUserInfoByEmail(answer["user"])
answer["user"] = data_user
if "forum" in related:
answer["forum"] = getForumDetailsByShortName(answer["forum"])
if "thread" in related:
answer["thread"] = getThreadDetailsByID(answer["thread"], [])
# logging.info(" ===========Answer getPostByID() : ")
# logging.info(answer)
#logging.info(" ===================================")
return answer
def getPostDetailsByID(postID, related):
sql = "SELECT * FROM Post WHERE idPost = %s"
cursor.execute(sql, [postID])
data = cursor.fetchone()
logging.info(data)
if (not data):
logging.info(" Thread not found")
return None
answer = {}
answer["id"] = data[0]
answer["parent"] = data[1]
answer["isApproved"] = data[2]
answer["isHighlighted"] = data[3]
answer["isEdited"] = data[4]
answer["isSpam"] = data[5]
answer["isDeleted"] = data[6]
answer["likes"] = data[7]
answer["dislikes"] = data[8]
answer["date"] = str(data[9])
answer["message"] = data[10]
answer["forum"] = getForumShortNameById(data[11])
answer["thread"] = data[12]
answer["user"] = getUserEmailByID(data[13])
answer["points"] = answer["likes"] - answer["dislikes"]
if "user" in related:
data_user = getUserInfoByEmail(answer["user"])
answer["user"] = data_user
if "forum" in related:
answer["forum"] = getForumDetailsByShortName(answer["forum"])
if "thread" in related:
answer["thread"] = getThreadDetailsByID(answer["thread"], [])
logging.info(" ===========Answer getPostByID() : ")
logging.info(answer)
logging.info(" ===================================")
return answer
def createUser():
tic = time()
# logging.info("================USER CREATION")
try:
email = request.json["email"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
name = request.json["name"]
except:
name = None
# logging.info("NAME IS None")
# logging.info("_____________________")
try:
username = request.json["username"]
except:
username = None
# logging.info("USERNAME IS None")
#logging.info("_____________________")
try:
about = request.json["about"]
except:
about = None
#logging.info("ABOUT IS None")
# logging.info("_____________________")
if (not isString([name, email, username, about])):
#logging.info("one or more params not string")
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idUser FROM User WHERE email = %s"
cursor.execute(sql, [email])
if (cursor.fetchone() != None):
#logging.info(email + " is already exists")
return json.dumps({"code": 5, "response": error_messages[5]})
#isAnonymous = False
#if ("isAnonymous" in request.json):
isAnonymous = request.json.get("isAnonymous", False)
sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, [username, email, name, about, isAnonymous])
sql = "SELECT MAX(idUser) FROM User"
cursor.execute(sql)
idU = cursor.fetchone()[0]
# data = {}
# data['about'] = about
# data['email'] = email
# data['id'] = idU
# data['isAnonymous'] = isAnonymous
# data['name'] = name
# data['username'] = username
data = {
'about': about,
'email': email,
'id': idU,
'isAnonymous': isAnonymous,
'username': username,
}
answer = {"code": 0, "response": data}
response = json.dumps(answer)
# logging.info("================SUCCESSFUL USER CREATION\n")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/user/create/ +++POST")
return response
def createPost():
tic = time()
# logging.info("================Post CREATION\n")
#logging.info("Request : ")
#logging.info(request.json)
#logging.info(request.json["thread"])
try:
thread = request.json["thread"]
message = request.json["message"]
date = request.json["date"]
user = request.json["user"]
forum = request.json["forum"]
#logging.info("Thread : " + str(thread))
#logging.info("Message : " + message)
#logging.info("Date : " + str(date))
# logging.info("User : "******"Forum : " + str(forum))
except:
return json.dumps({"code": 2, "response": error_messages[2]})
parent = request.json.get('parent', None)
isApproved = request.json.get('isApproved', False)
isHighlighted = request.json.get('isHighlighted', False)
isEdited = request.json.get('isEdited', False)
isSpam = request.json.get('isSpam', False)
isDeleted = request.json.get('isDeleted', False)
try:
id_Forum = getForumDetailsByShortName(forum)["id"]
id_User = getUserInfoByEmail(user)["id"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "INSERT INTO Post(parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, likes, dislikes, date, message, idForum, idThread, idAuthor) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, [
parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, 0, 0,
date, message, id_Forum, thread, id_User
])
sql = "SELECT MAX(idPost) FROM Post"
cursor.execute(sql)
idP = cursor.fetchone()[0]
answer = {}
answer["id"] = idP
answer["parent"] = parent
answer["isApproved"] = isApproved
answer["isHighlighted"] = isHighlighted
answer["isEdited"] = isEdited
answer["isSpam"] = isSpam
answer["isDeleted"] = isDeleted
answer["likes"] = 0
answer["dislikes"] = 0
answer["points"] = answer["likes"] - answer["dislikes"]
answer["date"] = date
answer["message"] = message
answer["forum"] = forum
answer["thread"] = thread
answer["user"] = user
response = json.dumps({"code": 0, "response": answer})
# logging.info(" Response : " + response)
#logging.info("================SUCCESSFUL Post CREATION\n")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/post/create/ +++POST")
return response
def validate_username(self, username):
username = "******" + username.data + "'"
cursor.execute("SELECT * FROM users WHERE username = " + username)
res = cursor.fetchone()
if res is not None:
raise ValidationError('Please use a different username.')
from werkzeug.security import check_password_hash
from app import cursor
from app.models import User, Item
id = 1
cursor.execute(
"insert into items (name, type,value,rare) values ('长剑','weapon',3,1) RETURNING id"
)
weapon_id = cursor.fetchone()[0]
print(type(weapon_id))
def createUser():
tic = time()
# logging.info("================USER CREATION")
try:
email = request.json["email"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
name = request.json["name"]
except:
name = None
# logging.info("NAME IS None")
# logging.info("_____________________")
try:
username = request.json["username"]
except:
username = None
# logging.info("USERNAME IS None")
#logging.info("_____________________")
try:
about = request.json["about"]
except:
about = None
#logging.info("ABOUT IS None")
# logging.info("_____________________")
if (not isString([name, email, username, about])):
#logging.info("one or more params not string")
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "SELECT idUser FROM User WHERE email = %s"
cursor.execute(sql, [email])
if (cursor.fetchone() != None):
#logging.info(email + " is already exists")
return json.dumps({"code": 5, "response": error_messages[5]})
#isAnonymous = False
#if ("isAnonymous" in request.json):
isAnonymous = request.json.get("isAnonymous", False)
sql = "INSERT INTO User(username, email, name, about, isAnonymous) VALUES (%s, %s, %s, %s, %s)"
cursor.execute(sql, [username, email, name, about, isAnonymous])
sql = "SELECT MAX(idUser) FROM User"
cursor.execute(sql)
idU = cursor.fetchone()[0]
# data = {}
# data['about'] = about
# data['email'] = email
# data['id'] = idU
# data['isAnonymous'] = isAnonymous
# data['name'] = name
# data['username'] = username
data = {
'about': about,
'email': email,
'id': idU,
'isAnonymous': isAnonymous,
'username': username,
}
answer = {"code": 0, "response": data}
response = json.dumps(answer)
# logging.info("================SUCCESSFUL USER CREATION\n")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/user/create/ +++POST")
return response
def createPost():
logging.info("================Post CREATION\n")
logging.info("Request : ")
logging.info(request.json)
logging.info(request.json["thread"])
try:
thread = request.json["thread"]
message = request.json["message"]
date = request.json["date"]
user = request.json["user"]
forum = request.json["forum"]
logging.info("Thread : " + str(thread))
logging.info("Message : " + message)
logging.info("Date : " + str(date))
logging.info("User : "******"Forum : " + str(forum))
except:
return json.dumps({"code": 2, "response": error_messages[2]})
parent = getOptionalParameterOrDefault(request.json, "parent", None)
isApproved = getOptionalParameterOrDefault(request.json, "isApproved", False)
isHighlighted = getOptionalParameterOrDefault(request.json, "isHighlighted", False)
isEdited = getOptionalParameterOrDefault(request.json, "isEdited", False)
isSpam = getOptionalParameterOrDefault(request.json, "isSpam", False)
isDeleted = getOptionalParameterOrDefault(request.json, "isDeleted", False)
try:
id_Forum = getForumDetailsByShortName(forum)["id"]
id_User = getUserInfoByEmail(user)["id"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "INSERT INTO Post(parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, likes, dislikes, date, message, idForum, idThread, idAuthor) " \
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
cursor.execute(sql, [parent, isApproved, isHighlighted, isEdited, isSpam, isDeleted, 0, 0, date, message, id_Forum, thread, id_User])
sql = "SELECT MAX(idPost) FROM Post"
cursor.execute(sql)
idP = cursor.fetchone()[0]
answer = {}
answer["id"] = idP
answer["parent"] = parent
answer["isApproved"] = isApproved
answer["isHighlighted"] = isHighlighted
answer["isEdited"] = isEdited
answer["isSpam"] = isSpam
answer["isDeleted"] = isDeleted
answer["likes"] = 0
answer["dislikes"] = 0
answer["points"] = answer["likes"] - answer["dislikes"]
answer["date"] = date
answer["message"] = message
answer["forum"] = forum
answer["thread"] = thread
answer["user"] = user
response = json.dumps({"code": 0, "response": answer})
logging.info(" Response : " + response)
logging.info("================SUCCESSFUL Post CREATION\n")
return response
