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()