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