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 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 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 __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 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 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 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 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 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 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 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 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 ChangePwd(self,NewPwd):
self.NewPwd = NewPwd
Library = MSSQL('Library')
Library.ExecNonQuery(f"UPDATE TB_Reader SET rdPwd='{self.NewPwd}' WHERE rdID={self.id}")
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
def EditBookInfo(self,bkID,EditDict):
self.bkID = bkID
self.EditDict = EditDict
Library = MSSQL('Library')
Library.ExecNonQuery("UPDATE TB_Book SET bkName = '新的测试名称' WHERE bkID = {self.bkID}")
