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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 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()
def changePasswordAndAvatar(self, username, new_password, new_typeAvt):
query_str = "UPDATE owner SET password = ?, typeAvt = ? WHERE username = ?"
connectDatabase = ConnectDatabase()
connectDatabase.cursor.execute(query_str, new_password, new_typeAvt,
username)
connectDatabase.connection.commit()
connectDatabase.close()
# thêm thông báo
icon = "icon-account.png"
titleNotification = "Chỉnh sửa thông tin"
content = "Avatar và/hoặc mật khẩu vừa được thay đổi"
Notification().create(titleNotification, username, icon, content)
def getMoreInformationPost(self, idPost):
connectDatabase = ConnectDatabase()
query_str = """
SELECT idPost, contentPost, locationRelate, numOfRoom, area, bathroom, kitchen, aircondition, balcony, priceElectric, priceWater, otherUtility, usernameAuthorPost, typeAccountAuthor, postDuration
FROM post
WHERE idPost = ?
"""
row = connectDatabase.cursor.execute(query_str, idPost).fetchone()
if row.typeAccountAuthor == "admin":
name = "Quản trị viên"
query_str = """
SELECT phoneNumber, address
FROM admin
LIMIT 1
"""
adminInfo = connectDatabase.cursor.execute(query_str).fetchone()
phoneNumber = adminInfo.phoneNumber
address = adminInfo.address
else:
query_str = """
SELECT fullname, phoneNumber, concat(addressWard, ", ", addressDistrict, ", ", addressProvince) as "address"
FROM owner WHERE username = ?
"""
ownerInfo = connectDatabase.cursor.execute(
query_str, row.usernameAuthorPost).fetchone()
name = ownerInfo.fullname
phoneNumber = ownerInfo.phoneNumber
address = ownerInfo.address
query_str = "SELECT image FROM image_post WHERE idPost = ? LIMIT 1"
image = connectDatabase.cursor.execute(query_str,
row.idPost).fetchval()
connectDatabase.close()
return {
"idPost": row.idPost,
"contentPost": row.contentPost,
"locationRelate": row.locationRelate,
"numOfRoom": row.numOfRoom,
"area": row.area,
"bathroom": row.bathroom,
"kitchen": row.kitchen,
"aircondition": row.aircondition,
"balcony": row.balcony,
"priceElectric": row.priceElectric,
"priceWater": row.priceWater,
"otherUtility": row.otherUtility,
"usernameAuthorPost": row.usernameAuthorPost,
"typeAccountAuthor": row.typeAccountAuthor,
"postDuration": row.postDuration,
"name": name,
"phoneNumber": phoneNumber,
"address": address,
"image": image
}
def getHistoryPost(self, usernameRenter):
query_str = """
SELECT DISTINCT(post.idPost) idPost, post.titlePost, post.priceItem, post.addressDetail, post.addressWard, post.addressDistrict, post.addressProvince
FROM history_view
JOIN post ON history_view.idPost = post.idPost
WHERE usernameRenter = ? AND history_view.status = ?
AND NOW() >= history_view.time
ORDER BY history_view.time DESC
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, usernameRenter, "yes").fetchall()
connectDatabase.close()
return [{"idPost": row.idPost, "image": self.getImagePost(row.idPost, "one")["image"], "titlePost": row.titlePost, "priceItem": row.priceItem, "addressDetail": row.addressDetail, "addressWard": row.addressWard, "addressDistrict": row.addressDistrict, "addressProvince": row.addressProvince} for row in rows]
def adminGetReview(self, status):
query_str = """
SELECT id, review.idPost, stars, fullname, content, DATE(time) time, titlePost, typeAvt, review.status
FROM review
JOIN renter ON renter.username = usernameRenter
JOIN post ON post.idPost = review.idPost
WHERE review.status = ? AND NOW() >= time
ORDER BY time
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, status).fetchall()
connectDatabase.close()
return [{"id": row.id, "status": row.status, "idPost": row.idPost, "stars": row.stars, "fullname": row.fullname, "content": row.content, "time": str(row.time), "titlePost": row.titlePost, "typeAvt": row.typeAvt} for row in rows]
def updateTotalFavoriteDefaultupdateTotalFavoriteDefault(self):
# update totalView()
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()
for i in range(1, 1000):
connectDatabase.cursor.execute(query_str, i, i, "yes")
connectDatabase.connection.commit()
connectDatabase.close()
def getFavoritePost(self, usernameRenter):
query_str = """
SELECT idPost, titlePost, priceItem, addressDetail, addressWard, addressDistrict, addressProvince
FROM post
WHERE idPost IN (
SELECT DISTINCT(idPost)
FROM favorite_post
WHERE usernameRenter = ? AND status = ? AND NOW() >= time
)
"""
connectDatabase = ConnectDatabase()
rows = connectDatabase.cursor.execute(query_str, usernameRenter, "yes").fetchall()
connectDatabase.close()
return [{"idPost": row.idPost, "image": self.getImagePost(row.idPost, "one")["image"], "titlePost": row.titlePost, "priceItem": row.priceItem, "addressDetail": row.addressDetail, "addressWard": row.addressWard, "addressDistrict": row.addressDistrict, "addressProvince": row.addressProvince} for row in rows]
