def bookBorrow(self):
bookID = self.lineEdit_bookID.text()
select = database.select_sql('Books',
['BookNo', 'BookName', 'Storage'])
self.db.execute(select)
resList = self.db.fetch_result(['BookNo', 'BookName', 'Storage'])
find = False
for item in resList:
if bookID == item['BookNo']:
find = True
contentStr = '是否要借 ' + item['BookName'] + ' ?'
reply = QMessageBox.question(self, '借书', contentStr,
QMessageBox.Yes | QMessageBox.No,
QMessageBox.Yes)
if reply == QMessageBox.Yes:
# already borrow this book
conditions = 'CardNo = ' + self.readerID
select = database.select_sql('LibraryRecords', ['BookNo'],
conditions)
self.db.execute(select)
res = self.db.fetch_result_list()
if bookID in res:
QMessageBox.about(self, '借书', '已经借过这本书了')
break
if int(item['Storage']) > 0:
# storage - 1
conditions = "BookNo = '" + bookID + "'"
update = database.update_sql('Books',
'Storage = Storage - 1',
conditions)
self.db.execute(update)
print('update succeed')
# insert record
insertList = [
self.readerID, bookID,
str(datetime.date.today()), self.adminName
]
for i in range(len(insertList)):
insertList[i] = "'" + insertList[i] + "'"
insert = database.insert_sql(
'LibraryRecords',
['CardNo', 'BookNo', 'LentDate', 'Operator'],
insertList)
self.db.execute(insert)
self.db.commit()
self.initTable()
contentStr = '借书成功！'
else:
contentStr = '库存量不足'
QMessageBox.about(self, '借书', contentStr)
break
if not find:
QMessageBox.about(self, '借书', '该书号不存在')
def button_enter(self):
id = self.lineEdit_account.text()
password = self.lineEdit_password.text()
select = database.select_sql('Users', ['UserID', 'Password', 'Name'])
self.db.execute(select)
res = self.db.fetch_result(['UserID', 'Password', 'Name'])
userexist = False
login = False
for item in res:
if item['UserID'] == str(id):
userexist = True
if item['Password'] == password:
login = True
name = item['Name']
else:
login = False
if userexist:
if login:
self.adminSignal.emit(name)
self.accept()
else:
self.reslabel.setText('密码错误')
self.lineEdit_password.clear()
else:
self.reslabel.setText('用户不存在')
self.lineEdit_password.clear()
self.lineEdit_account.clear()
def check(self, i, l):
condition = "BookNo = '%s'" % l[0]
select = database.select_sql('Books', ['BookNo'], condition)
self.db.execute(select)
res = self.db.fetch_result(['BookNo'])
if len(res) > 0:
QMessageBox.about(self, '批量入库', '第%d行图书书号已存在' % i)
return False
if len(l) != 9:
QMessageBox.about(self, '批量入库', '第%d行图书属性不全' % i)
return False
if not l[4].isdecimal():
QMessageBox.about(self, '批量入库', '第%d行年份必须为整数' % i)
return False
if not l[6].isdigit():
QMessageBox.about(self, '批量入库', '第%d行价格必须为数字' % i)
return False
if not l[7].isdecimal():
QMessageBox.about(self, '批量入库', '第%d行图书总量必须为整数' % i)
return False
if not l[8].isdecimal():
QMessageBox.about(self, '批量入库', '第%d行图书库存必须为整数' % i)
return False
if int(l[7]) < int(l[8]):
QMessageBox.about(self, '批量入库', '第%d行图书总量必须大于库存' % i)
return False
return True
def enter(self):
# check integrity
id = self.lineEdit_ID.text()
select = database.select_sql('LibraryCard', ['CardNo'])
self.db.execute(select)
resList = self.db.fetch_result_list()
if id in resList:
QMessageBox.about(self, '借书证管理', '用户名已存在')
return
# check NULL
name = self.lineEdit_name.text()
type = self.lineEdit_type.text()
department = self.lineEdit_department.text()
if name == '':
QMessageBox.about(self, '借书证管理', '姓名不能为空')
return
if type == '':
QMessageBox.about(self, '借书证管理', '类别不能为空')
return
if department == '':
QMessageBox.about(self, '借书证管理', '单位不能为空')
return
# insert
attrList = ['CardNo', 'Name', 'Department', 'CardType', 'UpdateTime']
insertList = [id, name, department, type, str(datetime.date.today())]
for i in range(len(insertList)):
insertList[i] = "'" + insertList[i] + "'"
insert = database.insert_sql('LibraryCard', attrList, insertList)
self.db.execute(insert)
self.db.commit()
QMessageBox.about(self, '借书证管理', '借书证增加成功！')
self.accept()
def initBox(self):
self.box_type.clear()
select = database.select_sql('Books', ['distinct BookType'])
self.db.execute(select)
oblist = self.db.fetch_result_list()
oblist.append('空')
self.box_type.addItems(oblist)
self.box_publisher.clear()
select = database.select_sql('Books', ['distinct Publisher'])
self.db.execute(select)
oblist = self.db.fetch_result_list()
oblist.append('空')
self.box_publisher.addItems(oblist)
self.box_type.setCurrentIndex(-1)
self.box_publisher.setCurrentIndex(-1)
def getReaderID(self, id):
# update label
self.readerID = id
select = database.select_sql('LibraryCard', ['CardNo', 'Name'])
self.db.execute(select)
res = self.db.fetch_result(['id', 'Name'])
for item in res:
if id == item['id']:
self.label_name.setText("当前图书证用户： " + item['Name'])
self.initList()
def initList(self):
self.borrowList.clear()
conditions = "LibraryRecords.BookNo = Books.BookNo and LibraryRecords.CardNo = '%s'" % self.readerID
conditions += " and isNULL(LibraryRecords.ReturnDate)"
select = database.select_sql('LibraryRecords, Books',
['Books.BookNo', 'BookName'], conditions)
self.db.execute(select)
reslist = self.db.fetch_result(['BookNo', 'BookName'])
mlist = []
for tup in reslist:
temp = "%s(%s)" % (tup['BookName'], tup['BookNo'])
mlist.append(temp)
self.borrowList.addItems(mlist)
def initTable(self):
self.table.clear()
select = database.select_sql('Books', '*')
self.db.execute(select)
attrlist = ['书号', '类型', '书名', '出版社', '出版时间',
'作者', '价格', '总藏书量', '库存', '最近更新']
reslist = self.db.fetch_result(attrlist)
self.table.setColumnCount(len(attrlist))
self.table.setRowCount(len(reslist))
self.table.setHorizontalHeaderLabels(attrlist)
for i in range(len(attrlist)):
for j in range(len(reslist)):
self.table.setItem(j, i, QTableWidgetItem(reslist[j][attrlist[i]]))
def initTable(self):
# update record
conditions = 'CardNo = ' + self.readerID
select = database.select_sql('LibraryRecords', '*', conditions)
self.db.execute(select)
attrlist = ['卡号', '书号', '借出日期', '归还日期', '操作人']
reslist = self.db.fetch_result(attrlist)
self.table.setColumnCount(len(attrlist))
self.table.setRowCount(len(reslist))
self.table.setHorizontalHeaderLabels(attrlist)
for i in range(len(attrlist)):
for j in range(len(reslist)):
self.table.setItem(j, i,
QTableWidgetItem(reslist[j][attrlist[i]]))
def initTable(self):
self.table.clear()
select = database.select_sql('LibraryCard', '*')
self.db.execute(select)
attrlist = ['卡号', '姓名', '单位', '类别', '更新时间']
reslist = self.db.fetch_result(attrlist)
print(reslist)
self.table.setColumnCount(len(attrlist))
self.table.setRowCount(len(reslist))
self.table.setHorizontalHeaderLabels(attrlist)
for i in range(len(reslist)):
for j in range(len(attrlist)):
self.table.setItem(i, j,
QTableWidgetItem(reslist[i][attrlist[j]]))
def query(self):
# conditions
conditions = ''
if self.lineEdit_name.text() != '':
conditions += "BookName = '" + self.lineEdit_name.text() + "' and "
if self.lineEdit_author.text() != '':
conditions += "Author = '" + self.lineEdit_author.text() + "' and "
if self.lineEdit_pricelow.text() != '':
conditions += 'Price >= ' + self.lineEdit_pricelow.text() + ' and '
if self.lineEdit_pricehigh.text() != '':
conditions += 'Price <= ' + self.lineEdit_pricehigh.text(
) + ' and '
if self.box_type.currentText() != '':
conditions += "BookType = '" + self.box_type.currentText(
) + "' and "
if self.box_publisher.currentText() != '':
conditions += "Publisher = '" + self.box_publisher.currentText(
) + "' and "
if self.box_yearlow.currentText() != '':
conditions += "Year >= " + self.box_yearlow.currentText() + " and "
if self.box_yearhigh.currentText() != '':
conditions += "Year <= " + self.box_yearhigh.currentText(
) + " and "
conditions = conditions[:-5]
select = database.select_sql('Books', '*', conditions)
self.db.execute(select)
attrlist = [
'书号', '类型', '书名', '出版社', '出版时间', '作者', '价格', '总藏书量', '库存', '最近更新'
]
reslist = self.db.fetch_result(attrlist)
self.table.setColumnCount(len(attrlist))
self.table.setRowCount(len(reslist))
self.table.setHorizontalHeaderLabels(attrlist)
for i in range(len(attrlist)):
for j in range(len(reslist)):
self.table.setItem(j, i,
QTableWidgetItem(reslist[j][attrlist[i]]))
def initLibraryCard(self):
select = database.select_sql('LibraryCard', '*')
self.db.execute(select)
res = self.db.fetch_result(
['CardNo', 'Name', 'Department', 'CardType', 'UpdateTime'])
return res
def initBookList(self):
select = database.select_sql('Books', '*')
self.db.execute(select)
res = self.db.fetch_result(['BookNo', 'BookType', 'BookName', 'Publisher', 'Year', 'Author',
'Price', 'Total', 'Storage', 'UpdateTime'])
return res
def enter(self):
# check integrity
id = self.lineEdit_BookNo.text()
select = database.select_sql('Books', ['BookNo'])
self.db.execute(select)
resList = self.db.fetch_result_list()
if id in resList:
QMessageBox.about(self, '图书入库', '书籍已存在')
return
# check NULL
name = self.lineEdit_name.text()
type = self.lineEdit_type.text()
publisher = self.lineEdit_publisher.text()
year = self.box_year.currentText()
author = self.lineEdit_author.text()
price = self.lineEdit_price.text()
total = self.lineEdit_total.text()
storage = self.lineEdit_storage.text()
if id == '':
QMessageBox.about(self, '图书入库', '书号不能为空')
return
if name == '':
QMessageBox.about(self, '图书入库', '书名不能为空')
return
if type == '':
QMessageBox.about(self, '图书入库', '类别不能为空')
return
if publisher == '':
QMessageBox.about(self, '图书入库', '出版社不能为空')
return
if year == '':
QMessageBox.about(self, '图书入库', '出版日期不能为空')
return
if author == '':
QMessageBox.about(self, '图书入库', '作者不能为空')
return
if price == '':
QMessageBox.about(self, '图书入库', '价格不能为空')
return
if total == '':
QMessageBox.about(self, '图书入库', '总量不能为空')
return
if storage == '':
QMessageBox.about(self, '图书入库', '库存不能为空')
return
if int(storage) > int(total):
QMessageBox.about(self, '图书入库', '库存不能大于总量')
return
# insert
attrList = ['BookNo', 'BookType', 'BookName', 'Publisher', 'Year', 'Author', 'Price', 'Total', 'Storage',
'UpdateTime']
insertList = [id, type, name, publisher, year, author, price, total, storage, str(datetime.date.today())]
for i in range(len(insertList)):
if i in [0, 1, 2, 3, 5, 9]:
insertList[i] = "'" + insertList[i] + "'"
insert = database.insert_sql('Books', attrList, insertList)
self.db.execute(insert)
self.db.commit()
QMessageBox.about(self, '图书入库', '入库成功！')
self.accept()