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]