def getTotalRecordCount(self): Library = MSSQL('Library') result = Library.ExecQuery("SELECT * FROM TB_Book") countResult = Library.ExecQuery("SELECT COUNT(*) FROM TB_Book") if result != []: self.totalRecord = countResult[0][0] print(self.totalRecord) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) #self.queryModel.setQuery("SELECT * FROM TB_Book",self.db) #self.totalRecord = self.queryModel.rowCount() return
def idEditChanged(self): rdID = self.idEdit.text() if (rdID == ""): self.nameEdit.clear() self.phoneEdit.clear() self.emailEdit.clear() self.passwordEdit.clear() # 查询对应ID,如果存在就更新form Library = MSSQL('Library') self.result = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdId='{rdID}'") if not self.result == []: rdTypeDIct = { 10: '教师', 20: '本科生', 21: '专科生', 30: '博士研究生', 31: '硕士研究生' } self.nameEdit.setText(self.result[0][1]) self.sexComboBox.setCurrentText(self.result[0][2]) self.typeComboBox.setCurrentText(rdTypeDIct[self.result[0][3]]) self.deptComboBox.setCurrentText(self.result[0][4]) self.phoneEdit.setText(self.result[0][5]) self.emailEdit.setText(self.result[0][6]) self.passwordEdit.setText(self.result[0][11]) self.adminRolesEdit.setText(str(self.result[0][12])) return
def bookIdEditChanged(self): BookCategoryDict = { 'TP': '自动化技术/计算机技术', 'TP3': '计算技术/计算机技术', 'TP31': '计算机软件', 'TP311': '程序设计/软件工程', 'TP312': '程序语言/算法语言', 'TP311.13': '数据库理论与系统', 'TP311.131': '数据库理论' } bookId = self.bookIdEdit.text() if (bookId == ""): self.bookNameEdit.clear() self.publisherEdit.clear() self.authorNameEdit.clear() self.Isbn.clear() # 查询对应书号,如果存在就更新form else: Library = MSSQL('Library') result = Library.ExecQuery( f"SELECT * FROM TB_Book WHERE bkID={bookId}") if not result == []: self.bookNameEdit.setText(result[0][2]) self.authorNameEdit.setText(result[0][3]) self.categoryComboBox.setCurrentText( BookCategoryDict[result[0][7]]) self.publisherEdit.setText(result[0][4]) self.Isbn.setText(result[0][6]) return
def NewBookIn(self, bkInfoList): ''' 避免传参过多,将参数打包成一个列表后再传递 示例: BM = BookManagement() bkInfoList = ['9787115313980','SQL必知必会','Ben Forta','人民邮电出版社','2013-05-01 00:00:00','9787115313980','TP311.131',0,239,29.00,'畅销全球的数据库入门经典','','在馆'] BM.NewBookIn(bkInfoList) ''' self.bkCode = bkInfoList[0] self.bkName = bkInfoList[1] self.bkAuthor = bkInfoList[2] self.bkPress = bkInfoList[3] self.bkDatePress = bkInfoList[4] self.bkISBN = bkInfoList[5] self.bkCatalog = bkInfoList[6] self.bkLanguage = bkInfoList[7] self.bkPages = bkInfoList[8] self.bkPrice = bkInfoList[9] # 这里略过了bkDateIn,用默认GetDate()函数就行 self.bkBrief = bkInfoList[10] self.bkCover = bkInfoList[11] self.bkStatus = bkInfoList[12] Library = MSSQL('Library') QueryList = Library.ExecQuery('Select bkID from TB_Book') if QueryList == []: self.bkID = 1 Library.ExecNonQuery( f"INSERT INTO TB_Book VALUES({self.bkID},'{self.bkCode}','{self.bkName}','{self.bkAuthor}','{self.bkPress}','{self.bkDatePress}','{self.bkISBN}','{self.bkCatalog}',{self.bkLanguage},{self.bkPages},{self.bkPrice},GETDATE(),'{self.bkBrief}','{self.bkCover}','{self.bkStatus}')") else: self.bkID = QueryList[len(QueryList)-1][0] self.bkID +=1 Library.ExecNonQuery( f"INSERT INTO TB_Book VALUES({self.bkID},'{self.bkCode}','{self.bkName}','{self.bkAuthor}','{self.bkPress}','{self.bkDatePress}','{self.bkISBN}','{self.bkCatalog}',{self.bkLanguage},{self.bkPages},{self.bkPrice},GETDATE(),'{self.bkBrief}','{self.bkCover}','{self.bkStatus}')")
def bookIdEditChanged(self): bookId = self.bookIdEdit.text() if (bookId == ""): self.bookNameEdit.clear() self.publisherEdit.clear() self.authNameEdit.clear() self.publishTime.clear() # 查询对应书号,如果存在就更新form Library = MSSQL('Library') self.result = Library.ExecQuery( f"SELECT * FROM TB_Book WHERE bkId='{bookId}'") if not self.result == []: BookCategoryDict = { 'TP': '自动化技术/计算机技术', 'TP3': '计算技术/计算机技术', 'TP31': '计算机软件', 'TP311': '程序设计/软件工程', 'TP312': '程序语言/算法语言', 'TP311.13': '数据库理论与系统', 'TP311.131': '数据库理论' } self.bookNameEdit.setText(self.result[0][2]) self.authNameEdit.setText(self.result[0][3]) self.categoryComboBox.setCurrentText( BookCategoryDict[self.result[0][7]]) self.publisherEdit.setText(self.result[0][4]) self.publishTime.setText(self.result[0][5].strftime('%Y-%m-%d')) return
def getTotalRecordCount(self): #self.queryModel.setQuery("SELECT COUNT(rdID) FROM TB_Reader") #self.totalRecord = self.queryModel.rowCount() Library = MSSQL('Library') countResult = Library.ExecQuery('SELECT COUNT(rdID) FROM TB_Reader') if countResult != []: self.totalRecord = countResult[0][0] return
def changePasswordButtonClicked(self): studentId = self.studentIdEdit.text() oldPassword = self.oldPasswordEdit.text() password = self.passwordEdit.text() confirmPassword = self.confirmPasswordEdit.text() if (studentId == "" or oldPassword == "" or password == "" or confirmPassword == ""): print( QMessageBox.warning(self, "警告", "输入不可为空,请重新输入", QMessageBox.Yes, QMessageBox.Yes)) return Library = MSSQL('Library') sql = f"SELECT * FROM TB_Reader WHERE rdID={studentId}" result = Library.ExecQuery(sql) # 如果用户不存在 if result == []: print( QMessageBox.warning(self, "警告", "该用户不存在,请重新输入", QMessageBox.Yes, QMessageBox.Yes)) self.studentIdEdit.clear() return # 如果密码错误 sql = f"SELECT * FROM TB_Reader WHERE rdPwd='{oldPassword}' AND rdID={studentId}" result = Library.ExecQuery(sql) if result == []: print( QMessageBox.warning(self, "警告", "原密码输入错误,请重新输入", QMessageBox.Yes, QMessageBox.Yes)) self.oldPasswordEdit.clear() return # 密码与确认密码不同 if (password != confirmPassword): print( QMessageBox.warning(self, "警告", "两次输入密码不同,请确认输入", QMessageBox.Yes, QMessageBox.Yes)) self.passwordEdit.clear() self.confirmPasswordEdit.clear() return # 修改密码 sql = f"UPDATE TB_Reader SET rdPwd='{password}' WHERE rdID={studentId}" result = Library.ExecNonQuery(sql) QMessageBox.information(self, "提醒", "修改密码成功,请登录系统!", QMessageBox.Yes, QMessageBox.Yes) self.close() return
def __init__(self, studentId): super().__init__() self.StudentId = studentId Library = MSSQL('Library') studentName = Library.ExecQuery( f"SELECT rdName FROM TB_Reader WHERE rdId={self.StudentId}") self.StudentName = studentName[0][0] self.resize(2200, 900) self.setWindowTitle("欢迎使用图书馆管理系统") self.setUpUI()
def returnedQuery(self): sql = f"SELECT * FROM TB_Borrow WHERE rdID={self.studentId} AND IsHasReturn=1" Library = MSSQL('Library') returnresult = Library.ExecQuery(sql) for i in range(0,len(returnresult)): for j in range(0,len(returnresult[0])): self.returnedQueryModel.setItem(i, j, QStandardItem(str(returnresult[i][j]))) #print(returnresult) return
def destoryLibraryCardButtonCicked(self): rdTypeDIct = {'教师': 10, '本科生': 20, '专科生': 21, '博士研究生': 30, '硕士研究生': 31} rdID = self.idEdit.text() rdName = self.nameEdit.text() rdSex = self.sexComboBox.currentText() rdType = self.typeComboBox.currentText() rdType = rdTypeDIct[rdType] rdDept = self.deptComboBox.currentText() rdPhone = self.phoneEdit.text() rdEmail = self.emailEdit.text() rdPhoto = self.photoEdit.text() rdStatus = self.statusComboBox.currentText() rdPassword = self.passwordEdit.text() rdAdminRoles = self.adminRolesEdit.text() rdAdminRoles = int(rdAdminRoles) Library = MSSQL('Library') result = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdID = '{rdID}'") BorrowResult = Library.ExecQuery( f"SELECT ldDateRetPlan FROM TB_Borrow WHERE rdID={rdID} AND IsHasReturn=0" ) if result == []: print( QMessageBox.warning(self, "警告", "该用户不存在,请检查ID输入!", QMessageBox.Yes, QMessageBox.Yes)) self.clearEdit() return elif BorrowResult != []: print( QMessageBox.warning(self, "警告", "用户存在为归还图书,请归还所有图书后再注销!", QMessageBox.Yes, QMessageBox.Yes)) else: Library.ExecNonQuery( f"UPDATE TB_Reader SET rdName='{rdName}',rdSex='{rdSex}',rdType={rdType},rdDept='{rdDept}',rdPhone='{rdPhone}',rdPhoto='{rdPhoto}',rdStatus='注销',rdPwd='{rdPassword}',rdAdminRoles='{rdAdminRoles}' WHERE rdID = {rdID}" ) print( QMessageBox.warning(self, "提示", "借书证注销成功!", QMessageBox.Yes, QMessageBox.Yes)) self.clearEdit() return
def BookDestroyOrSell(self,bkID,bkStatus): Library = MSSQL('Library') self.bkID = bkID self.bkStatus = bkStatus bkNowStatus = Library.ExecQuery(f"SELECT bkStatus FROM TB_Book WHERE bkID = {self.bkID}") bkNowStatus = bkNowStatus[0][0] #借出,遗失 if bkNowStatus == '在馆': Library.ExecNonQuery(f"UPDATE TB_Book SET bkStatus = '{self.bkStatus}' WHERE bkID = {self.bkID}") return '执行成功' else: return '图书已不在馆, 操作无法完成!'
def NewLibraryCard(self, rdInfoList): ''' [rdName] [rdSex] [rdType] [rdDept] [rdPhone] [rdEmail] [rdPhoto] [rdStatus] [rdBorrowQty] [rdPwd] [rdAdminRoles] ''' self.rdName = rdInfoList[0] self.rdSex = rdInfoList[1] self.rdType = rdInfoList[2] self.rdDept = rdInfoList[3] self.rdPhone = rdInfoList[4] self.rdEmail = rdInfoList[5] self.rdPhoto = rdInfoList[6] self.rdStatus = rdInfoList[7] self.rdBorrowQty = rdInfoList[8] self.rdPwd = rdInfoList[9] self.rdAdminRoles = rdInfoList[10] Library = MSSQL('Library') result = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdName='{self.rdName}' AND rdType={self.rdType} AND rdDept = '{self.rdDept}'" ) if not result == []: return '该借书证已存在!' else: result = Library.ExecQuery("SELECT rdID FROM TB_Reader") self.rdID = result[len(result) - 1][0] + 1 a = f"INSERT INTO TB_Reader VALUES({self.rdID},'{self.rdName}','{self.rdSex}',{self.rdType},'{self.rdDept}','{self.rdPhone}','{self.rdEmail}',GETDATE(),'{self.rdPhoto}','{self.rdStatus}',{self.rdBorrowQty},'{self.rdPwd}',{self.rdAdminRoles})" Library.ExecNonQuery( f"INSERT INTO TB_Reader VALUES({self.rdID},'{self.rdName}','{self.rdSex}',{self.rdType},'{self.rdDept}','{self.rdPhone}','{self.rdEmail}',GETDATE(),'{self.rdPhoto}','{self.rdStatus}',{self.rdBorrowQty},'{self.rdPwd}',{self.rdAdminRoles})" ) return '借书证添加成功'
def borrowedQuery(self): sql = f"SELECT * FROM TB_Borrow WHERE rdID={self.studentId} AND IsHasReturn=0" Library = MSSQL('Library') borrowresult = Library.ExecQuery(sql) for i in range(0,len(borrowresult)): for j in range(0,len(borrowresult[0])): self.borrowedQueryModel.setItem(i, j, QStandardItem(str(borrowresult[i][j]))) #self.borrowedQueryModel = QStandardItemModel(12,12) #self.borrowedQueryModel.setItem(row, column, QStandardItem(data)) #self.borrowedQueryModel.setQuery(sql) return
def Login(self,id,pwd): self.id = id self.pwd = pwd result = [] Library = MSSQL('Library') rdAdminRolesCodeAndrdStatus = Library.ExecQuery(f"SELECT rdAdminRoles,rdStatus FROM TB_Reader WHERE rdID={self.id} AND rdPwd='{self.pwd}'") if rdAdminRolesCodeAndrdStatus == []: print('输入的账号密码有误!') result.append(False) return result elif rdAdminRolesCodeAndrdStatus[0][1] !='有效': print('借书证挂失或被注销,无效登录!') result.append(False) return result else: print('登录成功!') rdAdminRolesCode = rdAdminRolesCodeAndrdStatus[0][0] result.append(True) PermissionCodeList = self.rdAdminRoles(rdAdminRolesCode) result.append(PermissionCodeList) return result
def addLibraryCardButtonCicked(self): rdTypeDIct = {'教师': 10, '本科生': 20, '专科生': 21, '博士研究生': 30, '硕士研究生': 31} #rdInfoList = ['朱晨光','男',21,'CS','13349745060','*****@*****.**','','有效',0,'123',15] rdName = self.nameEdit.text() rdSex = self.sexComboBox.currentText() rdType = self.typeComboBox.currentText() rdType = rdTypeDIct[rdType] print(rdType) rdDept = self.deptComboBox.currentText() rdPhone = self.phoneEdit.text() rdEmail = self.emailEdit.text() rdPhoto = self.photoEdit.text() rdStatus = self.statusComboBox.currentText() rdPassword = self.passwordEdit.text() rdAdminRoles = self.adminRolesEdit.text() Library = MSSQL('Library') result = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdName = '{rdName}' AND rdPhone = '{rdPhone}'" ) if (rdName == '' or rdPhone == ''): print( QMessageBox.warning(self, "警告", "关键字段为空,添加失败", QMessageBox.Yes, QMessageBox.Yes)) return else: rdInfoList = [ rdName, rdSex, rdType, rdDept, rdPhone, rdEmail, rdPhoto, rdStatus, 0, rdPassword, rdAdminRoles ] RM = ReaderManagement() result = RM.NewLibraryCard(rdInfoList) print( QMessageBox.warning(self, "通知", result, QMessageBox.Yes, QMessageBox.Yes)) self.clearEdit()
def editLibraryCardButtonCicked(self): rdTypeDIct = {'教师': 10, '本科生': 20, '专科生': 21, '博士研究生': 30, '硕士研究生': 31} #rdInfoList = ['朱晨光','男',21,'CS','13349745060','*****@*****.**','','有效',0,'123',15] rdID = self.idEdit.text() rdName = self.nameEdit.text() rdSex = self.sexComboBox.currentText() rdType = self.typeComboBox.currentText() rdType = rdTypeDIct[rdType] rdDept = self.deptComboBox.currentText() rdPhone = self.phoneEdit.text() rdEmail = self.emailEdit.text() rdPhoto = self.photoEdit.text() rdStatus = self.statusComboBox.currentText() rdPassword = self.passwordEdit.text() rdAdminRoles = self.adminRolesEdit.text() rdAdminRoles = int(rdAdminRoles) Library = MSSQL('Library') result = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdID = '{rdID}'") if result == []: print( QMessageBox.warning(self, "警告", "该用户不存在,请检查ID输入!", QMessageBox.Yes, QMessageBox.Yes)) self.clearEdit() return else: Library.ExecNonQuery( f"UPDATE TB_Reader SET rdName='{rdName}',rdSex='{rdSex}',rdType={rdType},rdDept='{rdDept}',rdPhone='{rdPhone}',rdPhoto='{rdPhoto}',rdStatus='{rdStatus}',rdPwd='{rdPassword}',rdAdminRoles='{rdAdminRoles}' WHERE rdID = {rdID}" ) print( QMessageBox.warning(self, "提示", "修改成功!", QMessageBox.Yes, QMessageBox.Yes)) self.clearEdit() return
def recordQuery(self, index): Library = MSSQL('Library') queryCondition = "" conditionChoice = self.condisionComboBox.currentText() if (conditionChoice == "按书名查询"): conditionChoice = 'bkName' elif (conditionChoice == "按书号查询"): conditionChoice = 'bkId' elif (conditionChoice == "按作者查询"): conditionChoice = 'bkAuthor' elif (conditionChoice == '按分类查询'): conditionChoice = 'bkCatalog' else: conditionChoice = 'bkPress' if (self.searchEdit.text() == ""): queryCondition = "select * from TB_Book" countQueryCondition = "SELECT COUNT(*) FROM TB_Book" #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( f"select top ({self.pageRecord}) * from TB_Book where bkID not in(select top ({index}) bkID from TB_Book) ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return # 得到模糊查询条件 temp = self.searchEdit.text() s = f"%{temp}%" queryCondition = ( f"SELECT * FROM TB_Book WHERE {conditionChoice} LIKE '%{temp}%' ORDER BY {conditionChoice} " ) countQueryCondition = ( f"SELECT * FROM TB_Book WHERE {conditionChoice} LIKE '%{temp}%' ORDER BY {conditionChoice} " ) print(queryCondition) #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) print(self.totalRecord) # 当查询无记录时的操作 #if(self.totalRecord==0): if (result == []): print( QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)) queryCondition = "select * from TB_Book" countQueryCondition = "select COUNT(*) from TB_Book" #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) #queryCondition = ("select * from TB_Book ORDER BY %s limit %d,%d " % (conditionChoice,index, self.pageRecord)) queryCondition = ( f"select top ({self.pageRecord}) * from TB_Book where bkID not in(select top ({index}) bkID from TB_Book) ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) '''queryCondition = ("SELECT * FROM TB_Book WHERE %s LIKE '%s' ORDER BY %s LIMIT %d,%d " % ( conditionChoice, s, conditionChoice,index, self.pageRecord))''' queryCondition = ( f"select top ({self.pageRecord}) * from TB_Book where {conditionChoice} LIKE '{s}' and bkID not in (select top ({index}) bkID from TB_Book where {conditionChoice} LIKE '{s}') ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return
def returnButtonClicked(self): # 获取书号,书号为空或并未借阅,则弹出错误 BookId = self.bookIdEdit.text() rdID = int(self.studentId) # BookId为空的处理 if (BookId == ""): print( QMessageBox.warning(self, "警告", "你所要还的书不存在,请查看输入", QMessageBox.Yes, QMessageBox.Yes)) return # 打开数据库 Library = MSSQL('Library') BorrowResult = Library.ExecQuery( f"SELECT ldDateRetPlan FROM TB_Borrow WHERE bkID={BookId} AND rdID={rdID} AND IsHasReturn=0" ) print(BorrowResult) if BorrowResult == []: print( QMessageBox.information(self, "提示", "您并未借阅此书,故无需归还", QMessageBox.Yes, QMessageBox.Yes)) else: ldDateRetPlan = BorrowResult[0][0] #还书语句块, 判断是否逾期 if (ldDateRetPlan > datetime.datetime.now()): #未逾期 Library.ExecNonQuery( f"UPDATE TB_Borrow SET ldDateRetAct=GETDATE(),IsHasReturn=1,OperatorRet='{self.studentName}'" ) Library.ExecNonQuery( f"UPDATE TB_Reader SET rdBorrowQty=rdBorrowQty-1 WHERE rdID = {rdID}" ) Library.ExecNonQuery( f"UPDATE TB_Book SET bkStatus = '在馆' WHERE bkID = {BookId}" ) print( QMessageBox.information(self, "提示", "归还成功!", QMessageBox.Yes, QMessageBox.Yes)) return else: rdType = Library.ExecQuery( f"SELECT rdType FROM TB_Reader WHERE rdID={rdID}") rdType = rdType[0][0] OverMoneyRate = Library.ExecQuery( f"SELECT PunishRate FROM TB_ReaderType WHERE rdType={rdType}" ) OverMoneyRate = OverMoneyRate[0][0] dt = (ldDateRetPlan - datetime.datetime.now()) for seq, char in enumerate(str(dt)): if char == ' ': break ldOverDay = int(str(dt)[0:seq]) ldOverMoney = ldOverDay * OverMoneyRate Library.ExecNonQuery( f"UPDATE TB_Borrow SET ldDateRetAct=GETDATE(),IsHasReturn=1,OperatorRet='{self.studentName}',ldOverDay={ldOverDay},ldOverMoney={ldOverMoney}" ) Library.ExecNonQuery( f"UPDATE TB_Reader SET rdBorrowQty=rdBorrowQty-1 WHERE rdID = {rdID}" ) Library.ExecNonQuery( f"UPDATE TB_Book SET bkStatus = '在馆' WHERE bkID = {BookId}" ) print( QMessageBox.warning( self, "警告", f"您已逾期{ldOverDay}天, 按规定需要交纳罚金{ldOverMoney}元", QMessageBox.Yes, QMessageBox.Yes)) return
def borrowButtonClicked(self): BookId = self.bookIdEdit.text() rdID = int(self.studentId) processType = self.processTypeComboBox.currentText() if (BookId == ""): print( QMessageBox.warning(self, "警告", "输入的ID不能为空, 请检查输入", QMessageBox.Yes, QMessageBox.Yes)) return # 打开数据库 Library = MSSQL('Library') bookResult = Library.ExecQuery( f"SELECT bkStatus FROM TB_Book WHERE bkID={BookId}") #查询图书状态 borrowQtyAndrdTypeResult = Library.ExecQuery( f"SELECT * FROM TB_Reader WHERE rdID={rdID}") #查询借阅者属性 borrowQty = borrowQtyAndrdTypeResult[0][10] rdType = borrowQtyAndrdTypeResult[0][3] #查询可借阅/续借数量, 天数 CanLendQtyAndCanLendDayAndCanContinueTimes = Library.ExecQuery( f"SELECT CanLendQty,CanLendDay,CanContinueTimes FROM TB_ReaderType WHERE rdType={rdType}" ) CanLendQty = CanLendQtyAndCanLendDayAndCanContinueTimes[0][0] CanLendDay = CanLendQtyAndCanLendDayAndCanContinueTimes[0][1] CanContinueTimes = CanLendQtyAndCanLendDayAndCanContinueTimes[0][2] if processType == '借书': #获取新书的借阅ID BorrowID = Library.ExecQuery( "SELECT COUNT(BorrowID) FROM TB_Borrow") BorrowID = BorrowID[0][0] + 1 IsHaveBorrowed = Library.ExecQuery( f"SELECT * FROM TB_Borrow WHERE rdID={rdID} AND bkID={BookId} AND IsHasReturn=0" ) print(IsHaveBorrowed != []) if borrowQty >= CanLendQty: #判断借书先决条件, 借书数目和可借数目 print( QMessageBox.warning(self, "警告", "已借书数目超过可借书上限,无法继续借书,请先还书", QMessageBox.Yes, QMessageBox.Yes)) elif bookResult == []: #判断书存不存在 print( QMessageBox.warning(self, "警告", "你所要借的书不存在, 请检查输入", QMessageBox.Yes, QMessageBox.Yes)) elif bookResult[0][0] == '借出' and IsHaveBorrowed == []: print( QMessageBox.warning(self, "警告", "所借的书已借出,请借阅其它图书", QMessageBox.Yes, QMessageBox.Yes)) elif bookResult[0][0] in ['销毁', '遗失', '变卖']: #判断书是否被处理掉了 print( QMessageBox.warning(self, "警告", "所借的书已遗失/销毁/变卖,请联系图书管理员", QMessageBox.Yes, QMessageBox.Yes)) elif IsHaveBorrowed != []: print( QMessageBox.warning(self, "警告", "您已经借过这本书了", QMessageBox.Yes, QMessageBox.Yes)) else: Library.ExecNonQuery( f"INSERT INTO TB_Borrow (BorrowID,rdID,bkID,ldContinueTimes,ldDateOut,ldDateRetPlan,IsHasReturn,OperatorLend) VALUES ({BorrowID},{rdID},{BookId},0,GETDATE(),GETDATE()+{CanLendDay},0,'{self.studentName}')" ) Library.ExecNonQuery( f"UPDATE TB_Reader SET rdBorrowQty=rdBorrowQty+1 WHERE rdID = {rdID}" ) Library.ExecNonQuery( f"UPDATE TB_Book SET bkStatus = '借出' WHERE bkID = {BookId}" ) print( QMessageBox.warning(self, "提示", "借书成功, 请注意按期归还", QMessageBox.Yes, QMessageBox.Yes)) self.bookIdEdit.clear() else: #续借 print('续借') result = Library.ExecQuery( f"SELECT * FROM TB_Borrow WHERE bkID = {BookId} AND rdID = {rdID}" ) if result == []: print( QMessageBox.warning(self, "警告", "您未借阅这本书,请借阅后重试", QMessageBox.Yes, QMessageBox.Yes)) return print('here1') BorrowInfo = Library.ExecQuery( f"SELECT rdID,ldContinueTimes,ldDateRetPlan,IsHasReturn FROM TB_Borrow WHERE bkID = {BookId}" ) bkID2rdID = BorrowInfo[0][0] ldContinueTimes = BorrowInfo[0][1] ldDateRetPlan = BorrowInfo[0][2] IsHasReturn = BorrowInfo[0][3] print('here') if ldContinueTimes >= CanContinueTimes: print( QMessageBox.warning(self, "警告", "超过续借次数上限, 无法续借, 请先归还后再借书", QMessageBox.Yes, QMessageBox.Yes)) elif ldDateRetPlan < datetime.datetime.now(): print( QMessageBox.warning(self, "警告", "已逾期, 无法续借, 请先归还后再借书", QMessageBox.Yes, QMessageBox.Yes)) else: Library.ExecNonQuery( f"UPDATE TB_Borrow SET ldContinueTimes=ldContinueTimes+1, ldDateRetPlan=GETDATE()+{CanLendDay}" ) print( QMessageBox.warning(self, "提示", "续借成功! ", QMessageBox.Yes, QMessageBox.Yes)) self.bookIdEdit.clear()
def recordQuery(self, index): Library = MSSQL('Library') #searchCondision = ['按ID查询', '按姓名查询', '按性别查询', '按学历查询', '按系别查询'] queryCondition = "" conditionChoice = self.condisionComboBox.currentText() if (conditionChoice == "按ID查询"): conditionChoice = 'rdID' elif (conditionChoice == "按姓名查询"): conditionChoice = 'rdName' elif (conditionChoice == "按性别查询"): conditionChoice = 'rdSex' elif (conditionChoice == '按学历查询'): conditionChoice = 'rdType' else: conditionChoice = 'rdDept' if (self.searchEdit.text() == ""): queryCondition = "select * from TB_Reader" countQueryCondition = "select COUNT(*) from TB_Reader" #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( f"select top ({self.pageRecord}) * from TB_Reader where rdID not in(select top ({index}) rdID from TB_Reader) ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return # 得到模糊查询条件 temp = self.searchEdit.text() s = f"%{temp}%" queryCondition = ( f"SELECT * FROM TB_Reader WHERE {conditionChoice} LIKE '%{temp}%' ORDER BY {conditionChoice} " ) countQueryCondition = ( f"SELECT COUNT(*) FROM TB_Reader WHERE {conditionChoice} LIKE '%{temp}%' ORDER BY {conditionChoice} " ) print(queryCondition) #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) print(self.totalRecord) # 当查询无记录时的操作 #if(self.totalRecord==0): if (result == []): print( QMessageBox.information(self, "提醒", "查询无记录", QMessageBox.Yes, QMessageBox.Yes)) queryCondition = "select * from TB_Reader" countQueryCondition = "Select COUNT(*) from TB_Reader" #self.queryModel.setQuery(queryCondition,self.db) #self.totalRecord = self.queryModel.rowCount() result = Library.ExecQuery(queryCondition) countResult = Library.ExecQuery(countQueryCondition) if result != []: self.totalRecord = countResult[0][0] self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) #queryCondition = ("select * from TB_Book ORDER BY %s limit %d,%d " % (conditionChoice,index, self.pageRecord)) queryCondition = ( f"select top ({self.pageRecord}) * from TB_Reader where rdID not in(select top ({index}) rdID from TB_Reader) ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return self.totalPage = int( (self.totalRecord + self.pageRecord - 1) / self.pageRecord) label = "/" + str(int(self.totalPage)) + "页" self.pageLabel.setText(label) queryCondition = ( f"select top ({self.pageRecord}) * from TB_Reader where {conditionChoice} LIKE '{s}' and rdID not in (select top ({index}) rdID from TB_Reader where {conditionChoice} LIKE '{s}') ORDER BY {conditionChoice}" ) #self.queryModel.setQuery(queryCondition,self.db) result = Library.ExecQuery(queryCondition) self.queryModel.removeRows(0, self.queryModel.rowCount()) for i in range(0, len(result)): for j in range(0, len(result[0])): self.queryModel.setItem(i, j, QStandardItem(str(result[i][j]))) self.setButtonStatus() return