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 send():
from app import cursor
cursor.execute('''SELECT productid,name,stock from Product where stock <50 ''')
stock = cursor.fetchall()
print stock
if len(stock) > 0:
while True:
msg = MIMEText('we need order somthing,pls contact us!', 'plain','utf-8')
msg['From'] = _format_addr('YuanClothShop <%s>' % from_addr)
msg['Subject'] = Header('OrderList', 'utf-8').encode()
with open("emails.txt","r") as f:
emails = f.readlines()
for to_addr in emails:
msg['To'] = _format_addr('Supplier<%s>' % to_addr)
server = smtplib.SMTP(smtp_server, 25)
server.set_debuglevel(1)
server.ehlo()
server.starttls()
server.login(from_addr, password)
server.sendmail(from_addr, [to_addr], msg.as_string())
server.quit()
break
def freeze_admin():
if (request.method == 'POST'):
print "freeze applications"
sql = "UPDATE main_table SET freeze_status = \'true\' WHERE status = \'submitted\'"
try:
cursor.execute(sql)
db.commit()
print sql
except:
print "Db error"
sql = "SELECT application_no, name, position_applied FROM main_table WHERE status = '%s';" % "submitted"
cursor.execute(sql)
rows1 = list(cursor.fetchall())
rows = []
for i in range(len(rows1)):
temp = [
rows1[i][0], rows1[i][1], rows1[i][2],
str(rows1[i][0]) + ".pdf"
]
rows.append(temp)
print rows
return render_template('admin_view_part1.html',
email_=session['email'],
rows=rows)
def delete_ip_raspberry():
if request.method == 'POST':
unidade = str(request.form['name_unidade'])
hostname = str(request.form['hostname'])
try:
sql = (f"DELETE FROM equipments WHERE hostname ='{hostname}'")
print(sql)
cursor.execute(sql)
# json = {'Status':f'MÉTODO POST ATIVO: RASPBERRY EXCLUÍDO: HOSTNAME: {hostname} -> {hospital}'}
# return jsonify(json)
flash(f'Raspberry: {hostname} foi deletetado com sucesso!', "success")
if unidade == 'HUR 1':
return redirect(url_for('ip_rasp_hur1'))
elif unidade == 'HGMI' :
return redirect(url_for('ip_rasp_hgmi'))
else:
return redirect(url_for('ip_rasp_anexo'))
except Error as e:
json_error = {'Error':f' {e}'}
return jsonify(json_error)
else:
request.method == 'GET'
return redirect(url_for('index'))
def setArticle(self, image_name, artic_name, artic_descr, artic_categ):
cursor.execute(
'INSERT INTO articles(image_name, artic_name, artic_descr) VALUES(%s, %s, %s)',
(image_name, artic_name, artic_descr))
cursor.execute('INSERT INTO categories(artic_categ) VALUES(%s)',
(artic_categ))
conn.commit()
cursor.execute(
'SELECT artic_id FROM articles WHERE artic_name=%s ORDER By artic_id DESC LIMIT 1',
(artic_name))
artic_ind = (cursor.fetchone())
cursor.execute(
'SELECT categ_id FROM categories WHERE artic_categ=%s ORDER By categ_id DESC LIMIT 1',
(artic_categ))
categ_ind = cursor.fetchone()
cursor.execute(
'INSERT INTO cat_art(artic_id, categ_id) VALUES(%s, %s)',
(artic_ind[0], categ_ind[0]))
conn.commit()
def get_products():
sql = "SELECT * FROM products"
status = 0,
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
print(type(result))
data = []
for rec in result:
product = {
'id': rec[0],
'product_name': rec[1],
'product_description': rec[2],
'product_price': rec[3],
'product_qty': rec[4],
'created_by': rec[5],
'actions': rec[6]
}
data.append(product)
status = 200
res = data
except:
print("Error: unable to fetch data")
return res, status
def loginval():
global email
global password
global displayname
error = ""
try:
email = request.form.get('email')
password = request.form.get('password')
cursor.execute(
"""SELECT * FROM `userfashion` WHERE `email` LIKE '{}'""".format(
email))
userfashion = cursor.fetchone()[3]
if len(userfashion) > 0:
if sha256_crypt.verify(password, userfashion):
cursor.execute(
"""SELECT * FROM `userfashion` WHERE `email` LIKE '{}'""".
format(email))
user = cursor.fetchone()
displayname = user[1]
session['user_id'] = True
flash('account logged in')
return redirect(url_for('home'))
else:
error = "Wrong Email or Password"
return render_template('login.html', error=error)
except Exception as e:
error = "invalid email. Pls register"
return render_template('login.html', error=error)
def getSubscriptions(idUser):
sql = "SELECT idThread FROM Subscription WHERE idUser = %s"
cursor.execute(sql, [idUser])
subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
# logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions))
return subscriptions
def dashboard_add():
location = request.form.get('searchlocation')
cursor.execute(
"""SELECT * FROM `admindatabase` WHERE `location` LIKE '{}'""".format(location))
posts = cursor.fetchall()
print(posts)
return render_template('userdashboard.html', posts=posts)
def delete(self, fields):
try:
sql = AllQuery.delete(self.table_name(self), fields)
cursor.execute(sql)
conn.commit()
except ConnectionError as error:
pass
def ip_hgmi():
#QUERY ALTERADA PARA TESTE
sql = (
"SELECT id, ur, ip, hostname, unidade, local, setor, ramal FROM equipments WHERE unidade = 'HGMI' AND raspberry LIKE 'N%' "
)
cursor.execute(sql)
dados = []
unidade = "HGMI"
for (id, ur, ip, hostname, unidade, local, setor, ramal) in cursor:
dados.append({
"id": id,
"ur": ur,
"ip": ip,
"hostname": hostname,
"unidade": unidade,
"local": local,
"setor": setor,
"ramal": ramal
})
# print(dados)
return render_template('public/maquinas.html',
dados=dados,
unidade=unidade)
def voteThread():
logging.info("================THREAD VOTE=====================")
if "vote" in request.json and "thread" in request.json:
vote = request.json["vote"]
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" vote : " + str(vote) + "; thread : " + str(thread))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s"
cursor.execute(sql, [thread])
answer = getThreadDetailsByID(thread, [])
response = json.dumps({"code": 0, "response": answer})
logging.info(" Response: ")
logging.info(response)
logging.info("================THREAD VOTE END=================")
return response
def getListPostsOfThread(thread, since, order, limit):
logging.info(" GETTING LIST POSTS BY THREAD")
sql = "SELECT * FROM Post WHERE idThread = %s"
params = [thread]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listPosts : " + sql)
logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, [])
logging.info(" GETTED POSTS : ")
logging.info(answer)
logging.info(" ==============")
return answer
def unsubscribeThread():
tic = time()
# logging.info("=====================UNSUBSCRIBE THREAD======================")
try:
user = request.json["user"]
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
# logging.info(" User : "******"; idThread : " + str(thread))
sql = "DELETE FROM Subscription WHERE idUser = %s AND idThread = %s"
cursor.execute(sql, [idUser, thread])
response = json.dumps({
"code": 0,
"response": {
"thread": thread,
"user": user
}
})
#logging.info(" Result : " + response)
#logging.info("=====================UNSUBSCRIBE THREAD SUCCESS======================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/thread/unsubscribe/ +++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 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 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 unfollow():
tic = time()
# logging.info("================USER UNFOLLOW========================")
try:
follower = request.json["follower"]
followee = request.json["followee"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
idFollower = getUserIdByEmail(follower)
idFollowee = getUserIdByEmail(followee)
except:
return json.dumps({"code": 1, "response": error_messages[1]})
# logging.info(" follower : " + str(idFollower))
# logging.info(" followee : " + str(idFollowee))
sql = "DELETE FROM Follower WHERE idFollower = %s AND idFollowing = %s"
cursor.execute(sql, [idFollower, idFollowee])
answer = getUserInfoByID(idFollower)
response = json.dumps({"code": 0, "response": answer})
# logging.info("================USER UNFOLLOW END====================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/user/unfollow/ +++POST")
return response
def getListPostsOfForum(forum, since, order, limit, related):
#logging.info(" GETTING LIST POSTS BY FORUM")
idForum = getForumIdByShortName(forum)
sql = "SELECT * FROM Post WHERE idForum = %s"
params = [idForum]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
# logging.info(" Final SQL listPosts : " + sql)
#logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, related)
#logging.info(" GETTED POSTS : ")
# logging.info(answer)
#logging.info(" ==============")
return answer
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 subscribeThread():
tic = time()
#logging.info("=======================SUBSCRIBE THREAD======================")
try:
user = request.json["user"]
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
#logging.info(" User : "******"; idThread : " + str(thread))
sql = "INSERT INTO Subscription(idUser, idThread) VALUES(%s,%s)"
try:
cursor.execute(sql, [idUser, thread])
except:
return json.dumps({"code": 5, "response": error_messages[5]})
response = json.dumps({"code": 0, "response": {"thread": thread, "user": user}})
#logging.info(" Result : " + response)
#logging.info("=======================SUBSCRIBE THREAD SUCCESS======================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/subscribe/ +++POST")
return response
def follow():
logging.info("================USER FOLLOW========================")
try:
follower = request.json["follower"]
followee = request.json["followee"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
idFollower = getUserIdByEmail(follower)
idFollowee = getUserIdByEmail(followee)
except:
return json.dumps({"code": 1, "response": error_messages[1]})
logging.info(" follower : " + str(idFollower))
logging.info(" followee : " + str(idFollowee))
sql = "INSERT INTO Follower(idFollower, idFollowing) VALUES(%s, %s)"
try:
cursor.execute(sql, [idFollower, idFollowee])
except:
json.dumps({"code": 5, "response": error_messages[5]})
emailsFollowers = getFollowerEmails(idFollower, None, None, None)
emailsFollowing = getFollowingEmails(idFollower, None, None, None)
userInfo = getUserInfoByID(idFollower)
userInfo["followers"] = emailsFollowers
userInfo["following"] = emailsFollowing
userInfo["subscriptions"] = []
logging.info("Response : ")
logging.info(userInfo)
response = json.dumps({"code": 0, "response": userInfo})
logging.info("================USER FOLLOW SUCCESS================\n")
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 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 voteThread():
logging.info("================THREAD VOTE=====================")
if "vote" in request.json and "thread" in request.json:
vote = request.json["vote"]
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" vote : " + str(vote) + "; thread : " + str(thread))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s"
cursor.execute(sql, [thread])
answer = getThreadDetailsByID(thread, [])
response = json.dumps({"code": 0, "response": answer})
logging.info(" Response: ")
logging.info(response)
logging.info("================THREAD VOTE END=================")
return response
def getSubscriptions(idUser):
sql = "SELECT idThread FROM Subscription WHERE idUser = %s"
cursor.execute(sql, [idUser])
subscriptions = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
logging.info(" Subscriptions of USER (" + str(idUser) + ") : " + str(subscriptions))
return subscriptions
def votePost():
logging.info("================POST VOTE=====================")
if "vote" in request.json and "post" in request.json:
vote = request.json["vote"]
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
logging.info(" vote : " + str(vote) + "; post : " + str(post))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Post SET" + addition + " WHERE idPost = %s"
cursor.execute(sql, [post])
answer = getPostDetailsByID(post, [])
response = json.dumps({"code": 0, "response": answer})
logging.info(" Response: ")
logging.info(response)
logging.info("================POST VOTE END=================")
return response
def unsubscribeThread():
tic = time()
# logging.info("=====================UNSUBSCRIBE THREAD======================")
try:
user = request.json["user"]
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
# logging.info(" User : "******"; idThread : " + str(thread))
sql = "DELETE FROM Subscription WHERE idUser = %s AND idThread = %s"
cursor.execute(sql, [idUser, thread])
response = json.dumps({"code": 0, "response": {"thread": thread, "user": user}})
#logging.info(" Result : " + response)
#logging.info("=====================UNSUBSCRIBE THREAD SUCCESS======================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/unsubscribe/ +++POST")
return response
def unfollow():
tic = time()
# logging.info("================USER UNFOLLOW========================")
try:
follower = request.json["follower"]
followee = request.json["followee"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
try:
idFollower = getUserIdByEmail(follower)
idFollowee = getUserIdByEmail(followee)
except:
return json.dumps({"code": 1, "response": error_messages[1]})
# logging.info(" follower : " + str(idFollower))
# logging.info(" followee : " + str(idFollowee))
sql = "DELETE FROM Follower WHERE idFollower = %s AND idFollowing = %s"
cursor.execute(sql, [idFollower, idFollowee])
answer = getUserInfoByID(idFollower)
response = json.dumps({"code": 0, "response": answer})
# logging.info("================USER UNFOLLOW END====================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/user/unfollow/ +++POST")
return response
def voteThread():
tic = time()
#logging.info("================THREAD VOTE=====================")
if "vote" in request.json and "thread" in request.json:
vote = request.json["vote"]
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
#logging.info(" vote : " + str(vote) + "; thread : " + str(thread))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
# logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s"
cursor.execute(sql, [thread])
answer = getThreadDetailsByID(thread, [])
response = json.dumps({"code": 0, "response": answer})
# logging.info(" Response: ")
# logging.info(response)
# logging.info("================THREAD VOTE END=================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/vote/ +++POST")
return response
def get_all_tasks():
sql = "SELECT * FROM tasks"
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
data = []
for rec in result:
task = {
'task_id': rec[0],
'robot_id': rec[1],
'task_name': rec[2],
'issued_time': json.dumps(rec[3], default=str),
'scheduled_time': json.dumps(rec[4], default=str),
'status': rec[5]
}
data.append(task)
status = 200
res = data
except Exception as e:
print("Error: unable to fetch data.")
print(e)
return res, status
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 getListPostsOfForum(forum, since, order, limit, related):
logging.info(" GETTING LIST POSTS BY FORUM")
idForum = getForumIdByShortName(forum)
sql = "SELECT * FROM Post WHERE idForum = %s"
params = [idForum]
if since:
sql += " AND date >= %s"
params.append(since)
sql += " ORDER BY date " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL listPosts : " + sql)
logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = cursor.fetchall()
answer = getArrayPostsFormDDictionary(result, related)
logging.info(" GETTED POSTS : ")
logging.info(answer)
logging.info(" ==============")
return answer
def login():
global users_dic_work
global users_dic_explore
if current_user.is_authenticated:
return redirect(url_for('index'))
form = LoginForm()
if form.validate_on_submit():
username = "******" + form.username.data + "'"
cursor.execute("SELECT * FROM users WHERE username = " + username)
res = cursor.fetchone()
if res is None:
flash('Invalid username')
return redirect(url_for('login'))
user = User(res)
if not user.check_password(form.password.data):
flash('Invalid password')
return redirect(url_for('login'))
login_user(user, remember=form.remember_me.data)
users_dic_work[current_user.id] = 0
users_dic_explore[current_user.id] = 0
next_page = request.args.get('next')
if not next_page or url_parse(next_page).netloc != '':
next_page = url_for('index')
return redirect(next_page)
return render_template('login.html', title='Sign In', form=form)
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 index():
if not current_user.is_authenticated:
return redirect(url_for('login'))
users_dic_work[current_user.id] = 0
users_dic_explore[current_user.id] = 0
if current_user.weapon_id is not None:
cursor.execute("SELECT * FROM items where id =" +
str(current_user.weapon_id))
res = cursor.fetchone()
weapon = Item(res)
else:
weapon = None
cursor.execute(
"select items.id, items.name, items.type, items.value, items.rare\
from items, users, armors\
where items.id=armors.armor_id and users.id=armors.user_id and users.id=%s"
% current_user.id)
res = cursor.fetchall()
defense = 0
if len(res) == 0:
armors = None
else:
armors = []
for i in res:
armor = Item(i)
defense = defense + armor.value
armors.append(armor)
return render_template("index.html",
title='Home Page',
weapon=weapon,
armors=armors,
defense=defense)
def subscribeThread():
tic = time()
#logging.info("=======================SUBSCRIBE THREAD======================")
try:
user = request.json["user"]
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
#logging.info(" User : "******"; idThread : " + str(thread))
sql = "INSERT INTO Subscription(idUser, idThread) VALUES(%s,%s)"
try:
cursor.execute(sql, [idUser, thread])
except:
return json.dumps({"code": 5, "response": error_messages[5]})
response = json.dumps({
"code": 0,
"response": {
"thread": thread,
"user": user
}
})
#logging.info(" Result : " + response)
#logging.info("=======================SUBSCRIBE THREAD SUCCESS======================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/thread/subscribe/ +++POST")
return response
def delete_user(data):
sql = "SELECT * FROM users WHERE user_email = '%s'" % data["user_email"]
print(sql)
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchone()
print(result[7])
if result[7] == 4 or result[7] == 15 or result[7] == 7:
sql2 = "DELETE FROM users WHERE user_email = '%s'" % data[
"user_email"]
print(sql2)
cursor.execute(sql2)
db.commit()
status = 200
res = "User deleted successfully"
else:
status = 201
res = "Not authorized"
except:
print("Error: unable to fetch data")
return res, status
def voteThread():
tic = time()
#logging.info("================THREAD VOTE=====================")
if "vote" in request.json and "thread" in request.json:
vote = request.json["vote"]
thread = request.json["thread"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
#logging.info(" vote : " + str(vote) + "; thread : " + str(thread))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
# logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Thread SET" + addition + " WHERE idThread = %s"
cursor.execute(sql, [thread])
answer = getThreadDetailsByID(thread, [])
response = json.dumps({"code": 0, "response": answer})
# logging.info(" Response: ")
# logging.info(response)
# logging.info("================THREAD VOTE END=================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/thread/vote/ +++POST")
return response
def create_user(user):
password = hash_password(user["password"])
sql = "INSERT INTO users(user_name, user_email, user_password, user_address, user_phone, created_by, actions) VALUES ('%s','%s','%s','%s','%s', %d, %d)" % (
user["user_name"], user["user_email"], password, user["user_address"],
user["user_phone"], user["created_by"], user["actions"])
status = 0
res = ''
try:
cursor.execute(sql)
db.commit()
status = 200
res = 'User created successfully'
except pymysql.Error as e:
try:
print("MySQL Error [%d]: %s") % (e.args[0], e.args[1])
return None
except IndexError:
print("MySQL Error: %s") % str(e)
return None
except:
db.rollback()
status = 401
res = 'Something went wrong. Try again'
return res, status
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 get_users():
sql = "SELECT * FROM users"
status = 0
res = ''
try:
cursor.execute(sql)
result = cursor.fetchall()
data = []
for rec in result:
user = {
'id': rec[0],
'user_name': rec[1],
'user_email': rec[2],
'user_address': rec[4],
'user_phone': rec[5],
'created_by': rec[6],
'actions': rec[7]
}
data.append(user)
status = 200
res = data
except:
print("Error: unable to fetch data")
return res, status
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 votePost():
tic = time()
#logging.info("================POST VOTE=====================")
if "vote" in request.json and "post" in request.json:
vote = request.json["vote"]
post = request.json["post"]
else:
return json.dumps({"code": 2, "response": error_messages[2]})
#logging.info(" vote : " + str(vote) + "; post : " + str(post))
if vote == 1:
addition = " likes = likes + 1"
elif vote == -1:
addition = " dislikes = dislikes + 1"
else:
#logging.info(" incorrect vote param : " + str(vote))
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Post SET" + addition + " WHERE idPost = %s"
cursor.execute(sql, [post])
answer = getPostDetailsByID(post, [])
response = json.dumps({"code": 0, "response": answer})
# logging.info(" Response: ")
# logging.info(response)
#logging.info("================POST VOTE END=================")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print(MyTime, "/db/api/post/vote/ +++POST")
return response
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 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 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 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 threadsList():
tic = time()
# logging.info("=====================================THREAD LIST BEGIN============================================")
try:
user = request.args.get("user")
#logging.info("User : "******"forum")
#logging.info("Forum : " + forum)
except:
forum = None
if not user and not forum:
return json.dumps({"code": 2, "response": error_messages[2]})
limit = getOptionalGetParameterOrDefault(request.args, "limit", None)
order = getOptionalGetParameterOrDefault(request.args, "order", "desc")
since = getOptionalGetParameterOrDefault(request.args, "since", None)
sql = "SELECT * FROM Thread WHERE 1 = 1 "
params = []
if user:
sql = sql + " AND idAuthor = %s"
idAuthor = getUserIdByEmail(user)
params.append(idAuthor)
if forum:
sql = sql + " AND idForum = %s"
idForum = getForumIdByShortName(forum)
params.append(idForum)
if since:
sql = sql + " AND DATE(date) >= %s" #TODO optimizate date query
params.append(since)
sql = sql + " ORDER BY date " + order
if limit:
sql = sql + " LIMIT " + str(limit)
# logging.info("FINAL SQL = " + sql)
#logging.info("FINAL PARAMS = " + str(params))
cursor.execute(sql, params)
data = cursor.fetchall()
answer = []
for item in data:
answer.append(getThreadDetailsByID(item[0], []))
response = json.dumps({"code": 0, "response": answer})
# logging.info("Response : ")
#logging.info(response)
#logging.info("=====================================THREAD LIST END============================================")
tac =time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/thread/list/")
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 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 updateThread():
logging.info("=======================UPDATE THREAD==========================")
try:
message = request.json["message"]
slug = request.json["slug"]
thread = request.json["thread"]
except:
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE Thread SET message = %s, slug = %s WHERE idThread = %s"
cursor.execute(sql, [message, slug, thread])
answer = getThreadDetailsByID(thread, [])
response = json.dumps({"code": 0, "response": answer})
logging.info("=======================UPDATE THREAD SUCCESS==================")
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 updateProfile():
logging.info("=======================UPDATING PROFILE========================")
try:
about = request.json["about"]
user = request.json["user"]
name = request.json["name"]
except:
logging.error(" no required fields")
return json.dumps({"code": 2, "response": error_messages[2]})
sql = "UPDATE User SET about = %s, name = %s WHERE email = %s"
cursor.execute(sql, [about, name, user])
result = getUserInfoByEmail(user)
response = json.dumps({"code": 0, "response": result})
logging.info(" Result : " + response)
logging.info("=======================UPDATING PROFILE SUCCESSFUL=============")
return response
def userListPosts():
tic = time()
# logging.info("================USER LISTPOSTS================")
try:
user = request.args.get("user")
except:
return json.dumps({"code": 2, "response": error_messages[2]})
limit = getOptionalGetParameterOrDefault(request.args, "limit", None)
order = getOptionalGetParameterOrDefault(request.args, "order", "desc")
since = getOptionalGetParameterOrDefault(request.args, "since", None)
idUser = getUserIdByEmail(user)
if not idUser:
return json.dumps({"code": 1, "response": error_messages[1]})
sql = "SELECT P.* FROM Post P INNER JOIN User U ON P.idAuthor = U.idUser WHERE U.idUser = %s"
params = [idUser]
if since:
params.append(since)
sql += " AND P.date >= %s"
sql += " ORDER BY P.date " + order
if limit:
params.append(int(limit))
sql += " LIMIT %s"
# logging.info(" Final SQL listPosts : " + sql)
# logging.info(" Final PARAMS listPosts : " + str(params))
cursor.execute(sql, params)
result = getArrayDictFromDoubleDictionary(cursor.fetchall())
# logging.info(" Results : ")
response = json.dumps({ "code": 0, "response": result})
# logging.info(" Response : " + response)
# logging.info("================USER LISTPOSTS END============")
tac = time()
MyTime = tac - tic
if MyTime > LimitTime:
print (MyTime, "/db/api/user/listPosts/")
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 getFollowingEmails(idUser, since, order, limit):
sql = "SELECT U.email FROM Follower F INNER JOIN User U ON F.idFollowing = U.idUser WHERE F.idFollower = %s"
params = [idUser]
if since:
sql += " AND U.idUser > %s"
params.append(int(since))
if not order:
order = "desc"
sql += " ORDER BY U.username " + order
if limit:
sql += " LIMIT %s"
params.append(int(limit))
logging.info(" Final SQL followingEmails : " + sql)
logging.info(" Final PARAMS followingEmails : " + str(params))
cursor.execute(sql, params)
emails = getArrayEmailsFromDoubleDictionary(cursor.fetchall())
logging.info(" EMAILS FOLLOWING OF USER (" + str(idUser) + ") : " + str(emails))
return emails
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 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
