def create(self, titlePost, contentPost, addressProvince, addressDistrict,
addressWard, addressDetail, locationRelate, itemType, numOfRoom,
priceItem, area, statusItem, bathroom, kitchen, aircondition,
balcony, priceElectric, priceWater, otherUtility,
usernameAuthorPost, typeAccountAuthor, postDuration,
listImages):
# typeAccountAuthor in ["owner", "admin"]
connectDatabase = ConnectDatabase()
connectDatabase.connection.autocommit = False
if typeAccountAuthor == "owner":
# chủ nhà trọ đăng bài => chờ admin duyệt
statusPost = "handling"
query_str = """
INSERT INTO post(titlePost, contentPost, addressProvince, addressDistrict, addressWard, addressDetail, locationRelate, itemType, numOfRoom, priceItem, area, statusItem, bathroom, kitchen, aircondition, balcony, priceElectric, priceWater, otherUtility, usernameAuthorPost, typeAccountAuthor, postDuration, createDate, statusPost, statusHired)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
connectDatabase.cursor.execute(
query_str, titlePost, contentPost, addressProvince,
addressDistrict, addressWard, addressDetail, locationRelate,
itemType, numOfRoom, priceItem, area, statusItem, bathroom,
kitchen, aircondition, balcony, priceElectric, priceWater,
otherUtility,
usernameAuthorPost, typeAccountAuthor, postDuration,
datetime.date(datetime.now()), statusPost, "ready")
else:
# admin đăng bài => không phải chờ duyệt
statusPost = "active"
query_str = """
INSERT INTO post(titlePost, contentPost, addressProvince, addressDistrict, addressWard, addressDetail, locationRelate, itemType, numOfRoom, priceItem, area, statusItem, bathroom, kitchen, aircondition, balcony, priceElectric, priceWater, otherUtility, usernameAuthorPost, typeAccountAuthor, postDuration, createDate, acceptDate, expireDate, statusPost, statusHired)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
connectDatabase.cursor.execute(
query_str, titlePost, contentPost, addressProvince,
addressDistrict, addressWard, addressDetail, locationRelate,
itemType, numOfRoom, priceItem, area, statusItem, bathroom,
kitchen, aircondition, balcony, priceElectric, priceWater,
otherUtility, usernameAuthorPost, typeAccountAuthor,
postDuration, datetime.date(datetime.now()),
datetime.date(datetime.now()),
datetime.date(datetime.now() + timedelta(days=postDuration)),
statusPost, "ready")
query_str = "SELECT MAX(idPost) FROM post"
idPost = connectDatabase.cursor.execute(query_str).fetchval()
query_str = """
INSERT INTO image_post(idPost, image)
VALUES (?, ?)
"""
for image in listImages:
connectDatabase.cursor.execute(query_str, idPost, image)
connectDatabase.connection.commit()
connectDatabase.close()
# thêm thông báo
icon = "icon-post.png"
titleNotification = "Đăng bài mới"
if typeAccountAuthor == "admin":
content = "Đăng bài thành công. Mã bài đăng: " + str(idPost)
else:
content = "Bài đăng " + str(idPost) + " đang chờ được duyệt"
Notification().create(titleNotification, usernameAuthorPost, icon,
content)
def checkAuthorPost(self, idPost, username):
connectDatabase = ConnectDatabase()
query_str = "SELECT COUNT(*) FROM post WHERE idPost = ? AND usernameAuthorPost = ?"
count = connectDatabase.cursor.execute(query_str, idPost,
username).fetchval()
connectDatabase.close()
return count == 1
def extendPost(self, idPost, postDuration, typeAccountAuthor,
usernameAuthorPost):
if typeAccountAuthor == "owner":
connectDatabase = ConnectDatabase()
query_str = """
UPDATE post SET postDuration = ?, statusPost = ?
WHERE idPost = ? AND usernameAuthorPost = ?
"""
connectDatabase.cursor.execute(query_str, postDuration, "extend",
idPost, usernameAuthorPost)
connectDatabase.connection.commit()
connectDatabase.close()
# thêm thông báo
icon = "icon-post.png"
titleNotification = "Gia hạn bài đăng"
content = "Bài đăng " + str(
idPost
) + " đang chờ chấp nhận gia hạn. Liên hệ sớm nhất với quản trị viên để thanh toán"
Notification().create(titleNotification, usernameAuthorPost, icon,
content)
else:
connectDatabase = ConnectDatabase()
query_str = "UPDATE post SET statusPost = ?, acceptDate = ?, expireDate = ? WHERE idPost = ? AND usernameAuthorPost = ?"
connectDatabase.cursor.execute(
query_str, "active", datetime.date(datetime.now()),
datetime.date(datetime.now() + timedelta(days=postDuration)),
idPost, usernameAuthorPost)
connectDatabase.connection.commit()
connectDatabase.close()
def signup(self, username, password, fullname, phoneNumber, email,
birthday, ID, imageID, addressProvince, addressDistrict,
addressWard, addressDetail, typeAvt):
"""
Đăng ký tài khoản của Chủ nhà trọ => Đưa vào danh sách chờ duyệt
Parameters
----------
None
Returns
----------
"""
query_str = """
INSERT INTO owner(username, password, fullname, phoneNumber, email, birthday, ID, imageID, addressProvince, addressDistrict, addressWard, addressDetail, typeAvt, status, createDate)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, username, password, fullname,
phoneNumber, email, birthday, ID,
imageID, addressProvince,
addressDistrict, addressWard,
addressDetail, typeAvt, "handling",
datetime.date(datetime.now()))
connectDatabase.connection.commit()
connectDatabase.close()
# thêm thông báo
icon = "icon-account.png"
titleNotification = "Đăng kí tài khoản"
content = "Tài khoản của bạn đang chờ admin phê duyệt. Trong khoảng thời gian này, bạn sẽ không thể đăng bài"
Notification().create(titleNotification, username, icon, content)
def getInformation(self, username):
query_str = """
SELECT phoneNumber, fullname, birthday, email, ID, addressProvince, addressDistrict, addressWard, addressDetail, status, createDate, acceptDate, typeAvt, imageID
FROM owner
WHERE username = ?
"""
connectDatabase = ConnectDatabase()
row = connectDatabase.cursor.execute(query_str, username).fetchone()
connectDatabase.close()
return {
"phoneNumber": row.phoneNumber,
"fullname": row.fullname,
"birthday": str(row.birthday),
"email": row.email,
"ID": row.ID,
"addressProvince": row.addressProvince,
"addressDistrict": row.addressDistrict,
"addressWard": row.addressWard,
"addressDetail": row.addressDetail,
"status": row.status,
"createDate": str(row.createDate),
"acceptDate": str(row.acceptDate),
"typeAvt": row.typeAvt,
"imageID": row.imageID
}
def checkUsername(self, username):
"""
Kiểm tra username đã tồn tại trong database hay chưa
Parameters
----------
username: str (Username muốn kiểm tra)
Returns
----------
str: Username đã tồn tại hay chưa ("exist", "not_exist")
"""
query_str = """
WITH user AS (
SELECT userName FROM admin
UNION SELECT userName FROM owner
UNION SELECT userName FROM renter
)
SELECT * FROM user
WHERE userName = ?
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, username)
count = rows.rowcount
if count == 0:
# chưa có tài khoản nào có tên đăng nhập trùng
connectDatabase.close()
return "not_exist"
# tồn tại tài khoản trùng tên đăng nhập
connectDatabase.close()
return "exist"
def unreadNotification(self, idNotification, usernameReceiver):
connectDatabase = ConnectDatabase()
query_str = "UPDATE notification SET status = ? WHERE id = ? AND usernameReceiver = ?"
connectDatabase.cursor.execute(query_str, "unread", idNotification,
usernameReceiver)
connectDatabase.connection.commit()
connectDatabase.close()
def fuzzywuzzySearch(self, stringSearch, limit = 1):
data = []
stringSearch = stringSearch.title()
connectDatabase = ConnectDatabase()
# find province
query_str = "SELECT DISTINCT(province) FROM address"
for row in connectDatabase.cursor.execute(query_str).fetchall():
score = fuzz.token_set_ratio(stringSearch, row.province)
if score > 70:
data.append({"address": row.province, "score": score})
# find district
query_str = """SELECT DISTINCT(CONCAT(district, ", ", province)) address FROM address"""
for row in connectDatabase.cursor.execute(query_str).fetchall():
score = fuzz.token_set_ratio(stringSearch, row.address)
if score > 70:
data.append({"address": row.address, "score": score})
# find ward
# query_str = """SELECT CONCAT(ward, ", ", district, ", ", province) address FROM address"""
# for row in connectDatabase.cursor.execute(query_str).fetchall():
# score = fuzz.token_set_ratio(stringSearch, row.address)
# if score > 70:
# data.append({"address": row.address, "score": score})
connectDatabase.close()
if data != []:
if limit == 1:
data.sort(reverse=True, key=lambda row: (row["score"], len(row["address"])))
else:
data.sort(reverse=True, key=lambda row: (row["score"]))
if len(data) > limit:
data = data[:limit]
return data
def handlingEditAccount(self, username, status):
# status = "deny" or status = "accept"
query_str = """
UPDATE owner_profile_scratch SET status = ?
WHERE username = ? AND status = ?
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, status, username, "handling")
connectDatabase.connection.commit()
if status == "accept":
query_str = """
SELECT phoneNumber, email, birthday, addressProvince, addressDistrict, addressWard, addressDetail, fullname
FROM owner_profile_scratch
WHERE username = ? AND status = ?
ORDER BY id DESC
LIMIT 1
"""
row = connectDatabase.cursor.execute(query_str, username, "accept").fetchone()
query_str = """
UPDATE owner SET phoneNumber = ?, email = ?, birthday = ?, addressProvince = ?, addressDistrict = ?, addressWard = ?, addressDetail = ?, fullname = ?
WHERE username = ? AND status = ?
"""
connectDatabase.cursor.execute(query_str, row.phoneNumber, row.email, row.birthday, row.addressProvince, row.addressDistrict, row.addressWard, row.addressDetail, row.fullname, username, "active")
connectDatabase.connection.commit()
connectDatabase.close()
def eventFavoritePost(self, idPost, usernameRenter, status="add"):
connectDatabase = ConnectDatabase()
query_str = """
UPDATE favorite_post
SET status = ?
WHERE idPost = ? AND usernameRenter = ? AND NOW() >= favorite_post.time
"""
connectDatabase.cursor.execute(query_str, "no", idPost, usernameRenter)
connectDatabase.connection.commit()
if status == "add":
query_str = """
INSERT INTO favorite_post(idPost, usernameRenter) VALUES (?, ?)
"""
connectDatabase.cursor.execute(query_str, idPost, usernameRenter)
connectDatabase.connection.commit()
# update totalFavorite
query_str = """
UPDATE post SET totalFavorite = (
SELECT COUNT(*)
FROM favorite_post
WHERE favorite_post.idPost = ? AND NOW() >= favorite_post.time AND favorite_post.status = ?
) WHERE idPost = ?
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, idPost, "yes", idPost)
connectDatabase.connection.commit()
connectDatabase.close()
def updateStatusHired(self, idPost, username, statusHired):
connectDatabase = ConnectDatabase()
query_str = "UPDATE post SET statusHired = ? WHERE idPost = ? AND usernameAuthorPost = ?"
connectDatabase.cursor.execute(query_str, statusHired, idPost,
username)
connectDatabase.connection.commit()
connectDatabase.close()
def checkLogin(self):
"""
Kiểm tra thông tin đăng nhập, lưu lại tên, loại tài khoản, avatar (nếu có)
Parameters
----------
None
Returns
----------
str: Kết quả kiểm tra ("active", "handling", "block", "null")
"""
query_str = """
WITH user AS (
SELECT userName, password, 'Quản trị viên' AS fullname, 'admin' AS typeAccount, typeAvt, 'active' AS status FROM admin
UNION SELECT userName, password, fullname, 'owner' AS typeAccount, typeAvt, status FROM owner
UNION SELECT userName, password, fullname, 'renter' AS typeAccount, typeAvt, status FROM renter
)
SELECT * FROM user
WHERE userName = ? AND password = ?
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, self.username,
self.password)
count = rows.rowcount
if count == 1:
row = rows.fetchone()
if row.status != "block":
self.type_account = row.typeAccount
self.type_avatar = row.typeAvt
self.fullname = row.fullname
connectDatabase.close()
return row.status
connectDatabase.close()
return "null"
def renterSendReport(self, idPost, usernameRenter, fakeInfo, fakePrice, content=""):
query_str = """
INSERT INTO report_post(idPost, usernameRenter, fakeInfo, fakePrice, content) VALUES (?, ?, ?, ?, ?)
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, idPost, usernameRenter, fakeInfo, fakePrice, content)
connectDatabase.connection.commit()
connectDatabase.close()
def changeStatusReport(self, id, status):
query_str = """
UPDATE report_post SET status = ? WHERE id = ?
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, status, id)
connectDatabase.connection.commit()
connectDatabase.close()
def isFavoritePost(self, usernameRenter, idPost):
connectDatabase = ConnectDatabase()
query_str = """
SELECT COUNT(*) FROM favorite_post WHERE usernameRenter = ? AND idPost = ? AND NOW() > time AND status = ?
"""
val = connectDatabase.cursor.execute(query_str, usernameRenter, idPost, "yes").fetchval()
connectDatabase.close()
return {"isFavorite": val!=0, "idPost": idPost}
def createReview(self, usernameRenter, stars, content, idPost, typeAvt):
query_str = """
INSERT INTO review(usernameRenter, stars, content, idPost)
VALUES(?, ?, ?, ?)
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, usernameRenter, stars, content, idPost)
connectDatabase.connection.commit()
connectDatabase.close()
def updateExpiredPost(self):
connectDatabase = ConnectDatabase()
query_str = """
UPDATE post SET statusPost = ?
WHERE statusPost = ? AND DATEDIFF(NOW(), expireDate) >= 0
"""
connectDatabase.cursor.execute(query_str, "expired", "active")
connectDatabase.connection.commit()
connectDatabase.close()
def searchAccountRenter(self, stringSearch):
query_str = """
SELECT status, "renter" as typeAccount, fullname, birthday, concat(addressWard, ", ", addressDistrict, ", ", addressProvince) address, "" as ID, "null" as imageID , email, username, phoneNumber, DATE(createDate) createDate, MATCH(fullname, phoneNumber) AGAINST (?) as score
FROM renter
ORDER BY score DESC LIMIT 5"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, stringSearch).fetchall()
connectDatabase.close()
return [{"status": row.status, "typeAccount": row.typeAccount, "fullname": row.fullname, "birthday": str(row.birthday), "address": row.address, "ID": row.ID, "imageID": row.imageID , "email": row.email, "username": row.username, "phoneNumber": row.phoneNumber, "createDate": str(row.createDate)} for row in rows]
def handlingRequestCreateAccountOwner(self, username, status):
# status = "active" or status = "deny"
query_str = """
UPDATE owner SET status = ?, acceptDate = ?
WHERE username = ? AND status = ?
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, status, datetime.date(datetime.now()), username, "handling")
connectDatabase.connection.commit()
connectDatabase.close()
def saveHistorySearch(self, addressSearch, usernameRenter):
connectDatabase = ConnectDatabase()
query_str = """
INSERT INTO history_search(addressSearch, usernameRenter)
VALUES(?, ?)
"""
connectDatabase.cursor.execute(query_str, addressSearch, usernameRenter)
connectDatabase.connection.commit()
connectDatabase.close()
def unFavoritePost(self, usernameRenter, idPost):
query_str = """
UPDATE favorite_post
SET status = ?
WHERE usernameRenter = ? AND idPost = ? AND NOW() >= time AND status = ?
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, "no", usernameRenter, idPost, "yes")
connectDatabase.connection.commit()
connectDatabase.close()
def updateReview(self, id, status, stars="", content=""):
connectDatabase = ConnectDatabase()
if stars == "" and content == "":
query_str = "UPDATE review SET status = ? WHERE id = ?"
connectDatabase.cursor.execute(query_str, status)
else:
query_str = "UPDATE review SET status = ?, stars = ?, content = ? WHERE id = ?"
connectDatabase.cursor.execute(query_str, status, stars, content, id)
connectDatabase.connection.commit()
connectDatabase.close()
def deleteAllHistoryPost(self, usernameRenter):
query_str = """
UPDATE history_view
SET status = ?
WHERE usernameRenter = ? AND NOW() >= time
"""
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, "no", usernameRenter)
connectDatabase.connection.commit()
connectDatabase.close()
def renterOrGuestGetReview(self, idPost):
query_str = """
SELECT id, stars, fullname, content, time, typeAvt
FROM review
JOIN renter ON renter.username = usernameRenter
WHERE NOW() >= time AND review.status = ? AND idPost = ?
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, "accept", idPost).fetchall()
connectDatabase.close()
return [{"id": row.id, "stars": row.stars, "fullname": row.fullname, "content": row.content, "time": str(row.time), "typeAvt": row.typeAvt} for row in rows]
def checkPassword(self, username, test_password):
query_str = """
SELECT COUNT(*)
FROM owner
WHERE username = ? AND password = ?
"""
connectDatabase = ConnectDatabase()
count = connectDatabase.cursor.execute(query_str, username,
test_password).fetchval()
connectDatabase.close()
return count == 1
def create(self, titleNotification, usernameReceiver, icon, content):
connectDatabase = ConnectDatabase()
query_str = """
INSERT INTO notification(titleNotification, usernameReceiver, icon, content, time)
VALUES (?, ?, ?, ?, ?)
"""
connectDatabase.cursor.execute(query_str, titleNotification,
usernameReceiver, icon, content,
datetime.now())
connectDatabase.connection.commit()
connectDatabase.close()
def getImagePost(self, idPost, limit = "all"):
connectDatabase = ConnectDatabase()
if limit == "one":
query_str = "SELECT image FROM image_post WHERE idPost = ? LIMIT 1"
image = connectDatabase.cursor.execute(query_str, idPost).fetchval()
connectDatabase.close()
return {"idPost": idPost, "image": image}
query_str = "SELECT image FROM image_post WHERE idPost = ?"
images = connectDatabase.cursor.execute(query_str, idPost).fetchall()
connectDatabase.close()
return {"idPost": idPost, "images": [image.image for image in images]}
def unblockPost(self, idPost):
connectDatabase = ConnectDatabase()
query_str = "SELECT statusPost, DATEDIFF(NOW(), expireDate) AS time FROM post WHERE idPost = ?"
row = connectDatabase.cursor.execute(query_str, idPost).fetchone()
if row.statusPost != "block":
connectDatabase.close()
else:
status = "active" if row.time <= 0 else "expired"
query_str = "UPDATE post SET statusPost = ? WHERE idPost = ?"
connectDatabase.cursor.execute(query_str, status, idPost)
connectDatabase.connection.commit()
connectDatabase.close()
def ownerGetReview(self, usernameRenter):
query_str = """
SELECT id, review.idPost, stars, fullname, content, time, titlePost, typeAvt, review.status
FROM review
JOIN renter ON renter.username = usernameRenter
JOIN post ON post.idPost = report_post.idPost
WHERE NOW() >= time AND usernameRenter = ?
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, usernameRenter).fetchall()
connectDatabase.close()
return [{"id": row.id, "idPost": row.idPost, "stars": row.stars, "fullname": row.fullname, "content": row.content, "time": str(row.time), "titlePost": row.titlePost, "status": row.status, "typeAvt": row.typeAvt} for row in rows]
def updateTotalViewDefault(self):
# update totalView()
query_str = """
UPDATE post SET totalView = (
SELECT COUNT(*) FROM history_view WHERE history_view.idPost = ? AND NOW() >= history_view.time
) WHERE idPost = ?
"""
connectDatabase = ConnectDatabase()
for i in range(1, 1000):
connectDatabase.cursor.execute(query_str, i, i)
connectDatabase.connection.commit()
connectDatabase.close()
