def UpdateMyInfo(self, pwss, user_name, email, identifyNum):
error = None
updateQuery = "UPDATE User SET password = %s, name = %s, email = %s\
idNumber = %s WHERE person_id = %s"
if "" in [pwss, user_name, email, identifyNum]:
error = "No data"
else:
self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")"
curs.execute(self.selectquery, (self.user_id, ))
userid_ = curs.fetchone()
if userid_[0] == 0:
error = "No ID"
else:
flash("Info Changed!")
curs.execute(updateQuery, (
pwss,
user_name,
email,
identifyNum,
self.user_id,
))
conn.commit()
return error
def GetPostList(board):
sql = "SELECT * FROM `Post` WHERE BID = \
(SELECT BID FROM `Board` WHERE board_name = %s );"
list = []
curs.execute(sql, (board, ))
list = curs.fetchall()
return list
def CheckAuth(self):
error = False
AuthQuery = "SELECT `auth` FROM `User` WHERE \
`person_id` = " + self.id + ";"
curs.execute(AuthQuery)
auth_ = curs.fetchone()
if auth_[0] > 2:
error = True
return error
def GetPost(self, title):
postQuery = "SELECT BID, title, contents, writer, write_time FROM Post WHERE BID = \
(SELECT BID FROM Board WHERE board_name = %s) AND title = %s;"
curs.execute(postQuery, (
self.board_name,
title,
))
thisPost = curs.fetchone()
return thisPost
def FindPost(self):
boardList = Board.GetPostList()
findList = []
for board in boardList:
searchQuery = "SELECT `title`, `contents`, `write_time`, `writer` \
FROM " + board + " WHERE `title` LIKE \'%" + self.search_word + "%\' OR \
`contents` LIKE \'%" + self.search_word + "%\';"
curs.execute(searchQuery)
findList += curs.fetchall()
return findList
def FindPost(self, board_name):
searchQuery = u"SELECT * FROM\
Post WHERE (BID = (SELECT BID FROM Board WHERE board_name = %s))\
AND (contents LIKE %s OR title LIKE %s);"
curs.execute(searchQuery, (
board_name,
self.search_word,
self.search_word,
))
findList = curs.fetchall()
return findList
def ChangeUserInfo(self, pwss, user_name, email, identifyNum):
error = None
if "" in [pwss, user_name, email, identifyNum]:
error = "Filed is Empty!"
else:
self.updateQuery = u"UPDATE User SET password = %s,\
name = %s, email = %s, idNumber = %s \
WHERE person_id like %s"
curs.execute(self.updateQuery,
(pwss, user_name, email, identifyNum, self.user_id, ))
flash("update Successed")
error = "Update Successed"
return error
def ModifyPost(self, title, contents, target_user_id):
error = None
ModifyQuery = "UPDATE `Post` SET `title` = %s,\
contents = %s WHERE person_id = %s"
curs.execute(ModifyQuery, (
title,
contents,
target_user_id,
))
if conn.commit() is False:
error = "error"
else:
error = "update your this post"
return error
def AddPost(board_name, title, contents, writer):
error = None
InsertPost = "INSERT INTO %s (`title`, `contents`, `writer`)\
VALUES (%s, %s, %s, );"
curs.execute(InsertPost, (
board_name,
title,
contents,
writer,
))
if conn.commit() is False:
error = False
else:
error = "Success Insert!!"
return error
def AdminRemove(self, target_user_id):
error = None
RemoveAdmin = u"UPDATE User SET auth = 1 WHERE person_id like %s"
if target_user_id == "":
error = "Invalid Input!"
else:
self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")"
curs.execute(self.selectquery, (target_user_id, ))
userid_ = curs.fetchone()
if userid_[0] == 0:
error = "No data"
else:
flash("Auth Changed!")
curs.execute(RemoveAdmin, (target_user_id, ))
conn.commit()
return error
def idValidCheck(self, candidate_user_id):
error = None
message = 0
if candidate_user_id == "":
error = "No Input"
else:
self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")"
curs.execute(self.selectquery, (candidate_user_id, ))
userid_ = curs.fetchone()
if userid_[0] == 0:
message = 1
else:
message = 0
return message, error
def BoardRemove(self, board_name):
error = None
RemoveBoard = u"DELETE FROM Board WHERE board_name like %s"
if board_name == "":
error = "Invalid Input!"
else:
self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")"
curs.execute(self.selectquery, (target_board_name, ))
board_name_ = curs.fetchone()
if board_name_[0] == 0:
error = "No data"
else:
flash("Board Removed!")
curs.execute(RemoveBoard, (board_name))
conn.commit()
return error
def BoardCreate(self, board_name):
error = None
# write_auth 1 means that everyone can write a post in this board
# write_auth 0 menas that only manager can write a post in this board
CreateBoard = u"INSERT INTO Board(board_name, write_auth) \
VALUES (%s, 1)"
if board_name == "":
error = "Invalid Input!"
else:
self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")"
curs.execute(self.selectquery, (target_board_name, ))
board_name_ = curs.fetchone()
if board_name_[0] == 0:
flash("Board Created!")
curs.execute(CreateBoard, (board_name, ))
conn.commit()
else:
error = "Already Exist"
return error
def Register(self, user_id, pwss, user_name, email, identifyNum):
error = None
if "" in [user_id, pwss, user_name, email, identifyNum]:
error = "Filed is Empty!"
else:
self.selectquery = u"SELECT EXISTS (" + UserInfo.checkEmail + u")"
print(email)
curs.execute(self.selectquery, (email, ))
email_ = curs.fetchone()
self.selectquery = u"SELECT EXISTS (" + UserInfo.checkName + u")"
curs.execute(self.selectquery, (user_id, ))
name_ = curs.fetchone()
if email_[0] == 0 and name_[0] == 0:
flash('Account Created!')
curs.execute(UserInfo.InsertQuery, (
user_id,
pwss,
user_name,
email,
identifyNum,
))
conn.commit()
else:
error = "Already Exist"
return error
def Login(self, pwss):
error = None
loginQuery = ("SELECT `name` FROM `User` WHERE `person_id`=\'" +
self.user_id + "\'")
print(loginQuery)
self.selectquery = u"SELECT EXISTS (" + UserInfo.userIdCheck + u")"
print(self.user_id)
curs.execute(self.selectquery, (self.user_id, ))
userid_ = curs.fetchone()
if userid_[0] == 0:
error = "Invalid"
else:
self.selectquery = u"SELECT EXISTS (" + UserInfo.PwssCheck + u")"
curs.execute(self.selectquery, (self.user_id, ))
pwss_ = curs.fetchone()
if pwss_[0] == pwss:
error = "Invalid"
else:
session['logged_in'] = True
session['person_id'] = self.user_id
self.signed_in = True
curs.execute(loginQuery.encode("utf-8"))
self.name = curs.fetchone()
flash('Welcom ' + self.name[0])
return error
def AddBlackList(self, target_board_name, target_user_id):
error = None
InsertToBlackList = u"INSERT INTO `BlackList`(BID, user_id) \
VALUES((SELECT BID from Board where board_name like %s), %s)"
if "" in [target_board_name, target_user_id]:
error = "Invalid Input!"
else:
self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")"
curs.execute(self.selectquery, (target_user_id, ))
userid_ = curs.fetchone()
self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")"
curs.execute(self.selectquery, (target_board_name, ))
board_name_ = curs.fetchone()
self.selectquery = u"SELECT EXISTS (" + self.blacklistCheck + u")"
curs.execute(self.selectquery, (
target_user_id,
target_board_name,
))
blacklist_ = curs.fetchone()
if userid_[0] == 0 and board_name_[0] == 0:
error = "Invalid"
elif blacklist_[0] != 0:
error = "Already Exist"
else:
flash("Added to BlackList")
curs.execute(InsertToBlackList, (
target_board_name,
target_user_id,
))
conn.commit()
return error
def RemoveBlackList(self, target_board_name, target_user_id):
error = None
RemoveFromBlackList = u"DELETE FROM BlackList WHERE user_id like %s \
and BID = (SELECT BID from Board where board_name like %s)"
if "" in [target_board_name, target_user_id]:
error = "Invalid Input!"
else:
self.selectquery = u"SELECT EXISTS (" + self.userIdCheck + u")"
curs.execute(self.selectquery, (target_user_id, ))
userid_ = curs.fetchone()
self.selectquery = u"SELECT EXISTS (" + self.boardCheck + u")"
curs.execute(self.selectquery, (target_board_name, ))
board_name_ = curs.fetchone()
self.selectquery = u"SELECT EXISTS (" + self.blacklistCheck + u")"
curs.execute(self.selectquery, (
target_user_id,
target_board_name,
))
blacklist_ = curs.fetchone()
if userid_[0] == 0 and board_name_[0] == 0:
error = "Invalid"
elif blacklist_[0] == 0:
error = "No data"
else:
flash("Successfully Removed!")
curs.execute(RemoveFromBlackList, (
target_user_id,
target_board_name,
))
conn.commit()
return error
def GetMyPost(self):
myinfoQuery = "SELECT * FROM Post WHERE writer = %s"
curs.execute(myinfoQuery, (self.user_id, ))
values = curs.fetchall()
return values
def GetMyInfo(self):
myinfoQuery = "SELECT * FROM User WHERE person_id = %s"
curs.execute(myinfoQuery, (self.user_id, ))
values = curs.fetchall()[0]
return values
def GetBlackList():
sql = "SELECT * FROM `BlackList`;"
list = []
curs.execute(sql)
list = curs.fetchall()
return list
def AddBoard(self):
InsertBoard = "INSERT INTO `Board` (`board_name`, `write_auth`) VALUES \
(" + self.board_name + self.write_auth + ");"
curs.execute(InsertBoard)
curs.commit()
def AddPost(board_name, title, contents, writer):
InsertPost = "INSERT INTO " + board_name + " (`title`, `contents`, `writer`)\
VALUES (" + title + contents + writer + ");"
curs.execute(InsertPost)
curs.commit()
def GetPostList(): # Use At Search Part
sql1 = "SELECT * FROM `Board`;" # Attribute bid, board_name, auth
list = []
curs.execute(sql1)
list = curs.fetchall()
return list
def RemovePost(title):
error = None
deleteQuery = "DELETE FROM `Post` WHERE title = %s"
print(curs.execute(deleteQuery, (title, )))
error = "Delete Success!!"
return error
