|
|
from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Date, Text, DateTime, Float
|
|
|
from sqlalchemy.orm import relationship, sessionmaker
|
|
|
from sqlalchemy.ext.declarative import declarative_base
|
|
|
from PySide6.QtWidgets import QApplication,QMainWindow,QInputDialog,QTableView,QHBoxLayout,QHeaderView,QLabel,QPushButton
|
|
|
from PySide6.QtWidgets import QWidget,QLineEdit,QMessageBox,QTableWidget,QFrame,QTableWidgetItem,QAbstractItemView,QDialog
|
|
|
from PySide6.QtCore import Qt
|
|
|
from main_ui import Ui_Form
|
|
|
from sqlalchemy import or_
|
|
|
from qt_material import apply_stylesheet
|
|
|
from signup_ui import signup_Ui_Form
|
|
|
from user_ui import Ui_Form_user
|
|
|
from datetime import datetime,timedelta
|
|
|
|
|
|
class signup_window(QWidget,signup_Ui_Form):
|
|
|
def __init__(self):
|
|
|
super().__init__()
|
|
|
self.setupUi(self)
|
|
|
class user_window(QWidget,Ui_Form_user):
|
|
|
def __init__(self):
|
|
|
super().__init__()
|
|
|
self.setupUi(self)
|
|
|
|
|
|
class mananger_Wiondow(QWidget,Ui_Form):
|
|
|
def __init__(self):
|
|
|
super().__init__()
|
|
|
self.setupUi(self)
|
|
|
self.titleLabel = QLabel("欢迎使用图书管理系统")
|
|
|
self.enter_button.clicked.connect(self.search_book)
|
|
|
self.book_add_pushButton.clicked.connect(self.add_book)
|
|
|
self.publish_add_pushButton.clicked.connect(self.add_pushlisher)
|
|
|
self.reader_add_pushButton.clicked.connect(self.add_reader)
|
|
|
self.publish_search_pushButton.clicked.connect(self.search_publish)
|
|
|
self.reader_search_pushButton.clicked.connect(self.search_reader)
|
|
|
self.tag_search_pushButton.clicked.connect(self.seadrch_tag)
|
|
|
self.tag_add_pushButton.clicked.connect(self.add_tag)
|
|
|
self.pushButton_3.clicked.connect(self.search_borrow)
|
|
|
self.signup_window = signup_window()
|
|
|
self.signup_window.show()
|
|
|
self.signup_window.manager_pushButton.clicked.connect(self.manager_signup)
|
|
|
|
|
|
self.user_window = user_window()
|
|
|
self.signup_window.user_pushButton.clicked.connect(self.user_signup_)
|
|
|
self.user_window.search_button.clicked.connect(self.search_book_user)
|
|
|
self.user_window.publish_search_pushButton.clicked.connect(self.return_book_user)
|
|
|
#用户账号
|
|
|
self.user_account = 1
|
|
|
|
|
|
def user_signup_(self):
|
|
|
account = self.signup_window.lineEdit.text()
|
|
|
password = self.signup_window.lineEdit_2.text()
|
|
|
session = Session()
|
|
|
reader = session.query(Reader).filter_by(id=account).first()
|
|
|
session.close()
|
|
|
if not account or not password:
|
|
|
# 如果账号或密码为空，则弹出警告
|
|
|
QMessageBox.warning(self,"登录失败", "账号或密码不能为空")
|
|
|
return
|
|
|
elif reader:
|
|
|
self.user_account = account
|
|
|
self.signup_window.hide()
|
|
|
self.user_window.show()
|
|
|
elif not reader:
|
|
|
QMessageBox.warning(self,"登录失败", "用户不存在")
|
|
|
|
|
|
def manager_signup(self):
|
|
|
account = self.signup_window.lineEdit.text()
|
|
|
password = self.signup_window.lineEdit_2.text()
|
|
|
if not account or not password:
|
|
|
# 如果账号或密码为空，则弹出警告
|
|
|
QMessageBox.warning(self,"登录失败", "账号或密码不能为空")
|
|
|
return
|
|
|
elif account == '111' and password == '111':
|
|
|
self.signup_window.hide()
|
|
|
self.show()
|
|
|
elif account != '111' or password != '111':
|
|
|
QMessageBox.warning(self, "登录失败", "账号或密码错误")
|
|
|
#添加图书
|
|
|
def add_book(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
name = self.search_bookname.text()
|
|
|
id = self.search_book_id.text()
|
|
|
author = self.search_editor.text()
|
|
|
publisher = self.book_publisher.text()
|
|
|
tag = self.tag_lineedit.text()
|
|
|
number = self.book_number_lineedit.text()
|
|
|
price = self.book_price_lineEdit.text()
|
|
|
description = self.book_description_lineEdit.text()
|
|
|
|
|
|
if name and id and author and publisher and tag and number and price and description:
|
|
|
new_book = Book(id=id,title=name, author=author, publish_name=publisher, description =description,
|
|
|
price=price,tag = tag,number = number)
|
|
|
|
|
|
session.add(new_book)
|
|
|
tags = session.query(Tag).filter(Tag.name == tag).first()
|
|
|
new_relation =Book_tag(book_id = id ,tag_id = tags.id)
|
|
|
session.add(new_relation)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', '添加成功')
|
|
|
#清空
|
|
|
self.search_bookname.clear()
|
|
|
self.search_book_id.clear()
|
|
|
self.search_editor.clear()
|
|
|
self.book_publisher.clear()
|
|
|
self.tag_lineedit.clear()
|
|
|
self.book_number_lineedit.clear()
|
|
|
self.book_price_lineEdit.clear()
|
|
|
self.book_description_lineEdit.clear()
|
|
|
else:
|
|
|
QMessageBox.information(self, '提示', '请填写完整的书籍信息。')
|
|
|
session.close()
|
|
|
|
|
|
#搜索图书
|
|
|
def search_book(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
name = self.search_bookname.text()
|
|
|
id = self.search_book_id.text()
|
|
|
author = self.search_editor.text()
|
|
|
publisher = self.book_publisher.text()
|
|
|
# 初始化查询条件列表
|
|
|
conditions = []
|
|
|
# 如果 name 不为 None，则添加书名筛选条件
|
|
|
if name:
|
|
|
conditions.append(Book.title.like(f'%{name}%'))
|
|
|
# 如果 id 不为 None，则添加书籍 ID 筛选条件
|
|
|
if id:
|
|
|
conditions.append(Book.id == id)
|
|
|
# 如果 author 不为 None，则添加作者名筛选条件
|
|
|
if author:
|
|
|
conditions.append(Book.author.like(f'%{author}%'))
|
|
|
# 如果 publisher 不为 None，则添加出版社筛选条件
|
|
|
if publisher:
|
|
|
conditions.append(Book.publisher.like(f'%{publisher}%'))
|
|
|
# 如果有任何一个查询条件，则执行查询
|
|
|
if conditions:
|
|
|
books = session.query(Book).filter(or_(*conditions))
|
|
|
# 如果找到了匹配的书籍
|
|
|
row = 0
|
|
|
for book in books:
|
|
|
publishers = session.query(Publisher).filter(Publisher.name==book.publish_name)
|
|
|
for publisher in publishers:
|
|
|
publisher_name = publisher.name
|
|
|
|
|
|
book_tags = session.query(Book_tag).filter(Book_tag.book_id == book.id)
|
|
|
|
|
|
for book_tag in book_tags:
|
|
|
print(book_tag.tag_id)
|
|
|
tags = session.query(Tag).filter(Tag.id == book_tag.tag_id)
|
|
|
for tag in tags:
|
|
|
tag_temp = tag.name
|
|
|
self.tableWidget.setItem(row, 0, QTableWidgetItem(str(book.id)))
|
|
|
self.tableWidget.setItem(row, 1, QTableWidgetItem(book.title))
|
|
|
self.tableWidget.setItem(row, 2, QTableWidgetItem(book.author))
|
|
|
try:
|
|
|
self.tableWidget.setItem(row, 3, QTableWidgetItem(publisher_name))
|
|
|
except UnboundLocalError:
|
|
|
self.tableWidget.setItem(row, 3, QTableWidgetItem('None'))
|
|
|
self.tableWidget.setItem(row, 4, QTableWidgetItem(tag_temp))
|
|
|
self.tableWidget.setItem(row, 5, QTableWidgetItem(str(book.description)))
|
|
|
self.tableWidget.setItem(row, 7, QTableWidgetItem(str(book.number)))
|
|
|
self.tableWidget.setItem(row, 6, QTableWidgetItem(str(book.price)))
|
|
|
self.tableWidget.setCellWidget(row,8, self.buttonForRow("book"))
|
|
|
row += 1
|
|
|
self.tableWidget.show()
|
|
|
if not row:
|
|
|
#如果没有找到匹配的书籍，弹出提示框
|
|
|
QMessageBox.information(self, '提示', '没有找到匹配的书籍。')
|
|
|
else:
|
|
|
# 如果没有任何一个查询条件，则提示用户输入查询关键字
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
# 关闭数据库连接
|
|
|
session.close()
|
|
|
|
|
|
|
|
|
#添加标签
|
|
|
def add_tag(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
tag_type = self.tag_type_lineEdit.text()
|
|
|
tag = self.tag_lineEdit.text()
|
|
|
|
|
|
if tag_type and tag:
|
|
|
tag_types = session.query(Tag_category).filter(Tag_category.name == tag_type).first()
|
|
|
print(tag_types.id)
|
|
|
if tag_types:
|
|
|
tags = session.query(Tag).filter(Tag.name == tag).first()
|
|
|
if tags :
|
|
|
QMessageBox.information(self, '提示', '该标签已存在。')
|
|
|
else:
|
|
|
new_tag = Tag(name = tag,category_id = tag_types.id)
|
|
|
session.add(new_tag)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', '添加成功')
|
|
|
#清空
|
|
|
self.tag_type_lineEdit.clear()
|
|
|
self.tag_lineEdit.clear()
|
|
|
else:
|
|
|
QMessageBox.information(self, '提示', '请填写完整的标签信息。')
|
|
|
session.close()
|
|
|
|
|
|
def seadrch_tag(self):
|
|
|
tag_types = self.tag_type_lineEdit.text()
|
|
|
tags = self.tag_lineEdit.text()
|
|
|
if tag_types:
|
|
|
session = Session()
|
|
|
row = 0
|
|
|
tags_= session.query(Tag_category).filter(Tag_category.name == tag_types)
|
|
|
for tag in tags_:
|
|
|
if not tags:
|
|
|
tag_= session.query(Tag).filter(Tag.category_id == tag.id).all()
|
|
|
if tags:
|
|
|
tag_= session.query(Tag).filter(Tag.category_id == tag.id,Tag.name == tags)
|
|
|
for it in tag_:
|
|
|
self.tableWidget_3.setItem(row, 0, QTableWidgetItem(tag.name))
|
|
|
self.tableWidget_3.setItem(row, 2, QTableWidgetItem(it.name))
|
|
|
self.tableWidget_3.setCellWidget(row,3, self.buttonForRow("tag"))
|
|
|
self.tableWidget_3.setCellWidget(row,1, self.buttonForRow("tag_type"))
|
|
|
row += 1
|
|
|
self.tableWidget_3.show()
|
|
|
self.tag_type_lineEdit.clear()
|
|
|
self.tag_lineEdit.clear()
|
|
|
if not row:
|
|
|
QMessageBox.information(self, "提示", "没有找到匹配的标签分类！")
|
|
|
if not tag_types and tags:
|
|
|
session = Session()
|
|
|
row = 0
|
|
|
tags_= session.query(Tag).filter(Tag.name == tags)
|
|
|
for tag in tags_:
|
|
|
print(tag.id)
|
|
|
tag_= session.query(Tag_category).filter(Tag_category.id == tag.category_id).first()
|
|
|
self.tableWidget_3.setItem(row, 2, QTableWidgetItem(tag.name))
|
|
|
self.tableWidget_3.setItem(row, 0, QTableWidgetItem(tag_.name))
|
|
|
self.tableWidget_3.setCellWidget(row,3, self.buttonForRow("tag"))
|
|
|
self.tableWidget_3.setCellWidget(row,1, self.buttonForRow("tag_type"))
|
|
|
row += 1
|
|
|
self.tableWidget_3.show()
|
|
|
self.tag_type_lineEdit.clear()
|
|
|
self.tag_lineEdit.clear()
|
|
|
if not row:
|
|
|
QMessageBox.information(self, "提示", "没有找到匹配的标签！")
|
|
|
if not tag_types and not tags:
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
session.close()
|
|
|
|
|
|
def search_reader(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
id = self.reader_id_lineEdit.text()
|
|
|
name = self.reader_name_lineEdit.text()
|
|
|
sex = self.reader_sex_lineEdit.text()
|
|
|
aders = self.reader_address_lineEdit.text()
|
|
|
read_tpye_id = self.reader_tpye_comboBox.currentIndex()+1
|
|
|
reader_admins= session.query(ReaderType).filter(ReaderType.id == read_tpye_id )
|
|
|
for reader_admin in reader_admins:
|
|
|
admin_max_borrow = reader_admin.max_borrow
|
|
|
admin_borrow_days = reader_admin.borrow_days
|
|
|
# 初始化查询条件列表
|
|
|
conditions = []
|
|
|
print(admin_borrow_days)
|
|
|
# 如果 name 不为 None，则添加书名筛选条件
|
|
|
if name:
|
|
|
conditions.append(Reader.name.like(f'%{name}%'))
|
|
|
if id:
|
|
|
conditions.append(Reader.id == id)
|
|
|
if sex:
|
|
|
conditions.append(Reader.gender.like(f'%{sex}%'))
|
|
|
if aders:
|
|
|
conditions.append(Reader.address.like(f'%{aders}%'))
|
|
|
|
|
|
if conditions:
|
|
|
readers = session.query(Reader).filter(or_(*conditions))
|
|
|
# 如果找到了匹配的书籍
|
|
|
row = 0
|
|
|
for reader in readers:
|
|
|
self.tableWidget_4.setItem(row, 1, QTableWidgetItem(str(reader.id)))
|
|
|
self.tableWidget_4.setItem(row, 0, QTableWidgetItem(reader.name))
|
|
|
self.tableWidget_4.setItem(row, 2, QTableWidgetItem(reader.gender))
|
|
|
self.tableWidget_4.setItem(row, 3, QTableWidgetItem(reader.address))
|
|
|
self.tableWidget_4.setItem(row, 4, QTableWidgetItem(str(admin_max_borrow )))
|
|
|
self.tableWidget_4.setItem(row, 5, QTableWidgetItem(str(admin_borrow_days)))
|
|
|
self.tableWidget_4.setItem(row, 6, QTableWidgetItem(str(read_tpye_id)))
|
|
|
self.tableWidget_4.setCellWidget(row,7, self.buttonForRow("reader"))
|
|
|
row += 1
|
|
|
self.tableWidget_4.show()
|
|
|
if not row:
|
|
|
QMessageBox.information(self, "提示", "没有找到匹配的读者！")
|
|
|
else:
|
|
|
# 如果没有任何一个查询条件，则提示用户输入查询关键字
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
# 关闭数据库连接
|
|
|
session.close()
|
|
|
|
|
|
def search_book_user(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
name = self.user_window.search_bookname.text()
|
|
|
id = self.user_window.search_book_id.text()
|
|
|
author = self.user_window.search_editor.text()
|
|
|
publisher = self.user_window.book_publisher.text()
|
|
|
# 初始化查询条件列表
|
|
|
conditions = []
|
|
|
# 如果 name 不为 None，则添加书名筛选条件
|
|
|
if name:
|
|
|
conditions.append(Book.title.like(f'%{name}%'))
|
|
|
# 如果 id 不为 None，则添加书籍 ID 筛选条件
|
|
|
if id:
|
|
|
conditions.append(Book.id == id)
|
|
|
# 如果 author 不为 None，则添加作者名筛选条件
|
|
|
if author:
|
|
|
conditions.append(Book.author.like(f'%{author}%'))
|
|
|
# 如果 publisher 不为 None，则添加出版社筛选条件
|
|
|
if publisher:
|
|
|
conditions.append(Book.publisher.like(f'%{publisher}%'))
|
|
|
# 如果有任何一个查询条件，则执行查询
|
|
|
if conditions:
|
|
|
books = session.query(Book).filter(or_(*conditions))
|
|
|
# 如果找到了匹配的书籍
|
|
|
row = 0
|
|
|
for book in books:
|
|
|
publishers = session.query(Publisher).filter(Publisher.name==book.publish_name)
|
|
|
for publisher in publishers:
|
|
|
publisher_name = publisher.name
|
|
|
|
|
|
book_tags = session.query(Book_tag).filter(Book_tag.book_id == book.id)
|
|
|
|
|
|
for book_tag in book_tags:
|
|
|
print(book_tag.tag_id)
|
|
|
tags = session.query(Tag).filter(Tag.id == book_tag.tag_id)
|
|
|
for tag in tags:
|
|
|
tag_temp = tag.name
|
|
|
self.user_window.tableWidget.setItem(row, 0, QTableWidgetItem(str(book.id)))
|
|
|
self.user_window.tableWidget.setItem(row, 1, QTableWidgetItem(book.title))
|
|
|
self.user_window.tableWidget.setItem(row, 2, QTableWidgetItem(book.author))
|
|
|
try:
|
|
|
self.user_window.tableWidget.setItem(row, 3, QTableWidgetItem(publisher_name))
|
|
|
except UnboundLocalError:
|
|
|
self.user_window.tableWidget.setItem(row, 3, QTableWidgetItem('None'))
|
|
|
self.user_window.tableWidget.setItem(row, 4, QTableWidgetItem(tag_temp))
|
|
|
self.user_window.tableWidget.setItem(row, 5, QTableWidgetItem(str(book.description)))
|
|
|
self.user_window.tableWidget.setItem(row, 7, QTableWidgetItem(str(book.number)))
|
|
|
self.user_window.tableWidget.setItem(row, 6, QTableWidgetItem(str(book.price)))
|
|
|
self.user_window.tableWidget.setCellWidget(row,8, self.borrowForRow())
|
|
|
row += 1
|
|
|
self.user_window.tableWidget.show()
|
|
|
if not row:
|
|
|
#如果没有找到匹配的书籍，弹出提示框
|
|
|
QMessageBox.information(self, '提示', '没有找到匹配的书籍。')
|
|
|
else:
|
|
|
# 如果没有任何一个查询条件，则提示用户输入查询关键字
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
# 关闭数据库连接
|
|
|
session.close()
|
|
|
#用户还书
|
|
|
def return_book_user(self):
|
|
|
session = Session()
|
|
|
borrows = session.query(Borrow).filter_by(reader_id=self.user_account).all()
|
|
|
row = 0
|
|
|
for borrow in borrows:
|
|
|
book_id = borrow.book_id
|
|
|
book_name = session.query(Book).filter_by(id = book_id).first()
|
|
|
self.user_window.tableWidget_2.setItem(row, 0, QTableWidgetItem(str(book_name.title)))
|
|
|
date_format = '%Y-%m-%d %H:%M:%S'
|
|
|
borrowed_date = datetime.strptime(str(borrow.borrow_date)[:-7], date_format)
|
|
|
remaining_days = borrow.borrow_date- (datetime.now() - borrowed_date)
|
|
|
remaining_days = remaining_days.day
|
|
|
self.user_window.tableWidget_2.setItem(row, 1, QTableWidgetItem(str(remaining_days)))
|
|
|
self.user_window.tableWidget_2.setCellWidget(row,2, self.returnForRow())
|
|
|
def add_reader(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
id = self.reader_id_lineEdit.text()
|
|
|
name = self.reader_name_lineEdit.text()
|
|
|
sex = self.reader_sex_lineEdit.text()
|
|
|
aders = self.reader_address_lineEdit.text()
|
|
|
read_tpye_id = self.reader_tpye_comboBox.currentIndex()+1
|
|
|
if id and name and sex and aders and read_tpye_id :
|
|
|
new_reader = Reader(id=id,name = name ,address = aders,gender=sex,reader_type_id=read_tpye_id)
|
|
|
session.add(new_reader)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', '添加读者成功。')
|
|
|
else:
|
|
|
QMessageBox.information(self, '提示', '请填写完整的读者信息。')
|
|
|
session.close()
|
|
|
|
|
|
|
|
|
def search_publish(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
name = self.publish_name_line.text()
|
|
|
email = self.publish_eamil_line.text()
|
|
|
address = self.publish_address_line.text()
|
|
|
# 初始化查询条件列表
|
|
|
conditions = []
|
|
|
# 如果 name 不为 None，则添加书名筛选条件
|
|
|
if name:
|
|
|
conditions.append(Publisher.name.like(f'%{name}%'))
|
|
|
# 如果 author 不为 None，则添加作者名筛选条件
|
|
|
if email:
|
|
|
conditions.append(Publisher.email.like(f'%{email}%'))
|
|
|
# 如果 publisher 不为 None，则添加出版社筛选条件
|
|
|
if address:
|
|
|
conditions.append(Publisher.address.like(f'%{address}%'))
|
|
|
# 如果有任何一个查询条件，则执行查询
|
|
|
if conditions:
|
|
|
publishes = session.query(Publisher).filter(or_(*conditions))
|
|
|
# 如果找到了匹配的书籍
|
|
|
row = 0
|
|
|
for publishe in publishes:
|
|
|
self.tableWidget_2.setItem(row, 0, QTableWidgetItem(publishe.name))
|
|
|
self.tableWidget_2.setItem(row, 2, QTableWidgetItem(publishe.email))
|
|
|
self.tableWidget_2.setItem(row, 1, QTableWidgetItem(publishe.address))
|
|
|
self.tableWidget_2.setCellWidget(row,3, self.buttonForRow("publisher"))
|
|
|
row += 1
|
|
|
self.tableWidget.show()
|
|
|
if not row:
|
|
|
QMessageBox.information(self, '提示', '没有找到匹配的书籍。')
|
|
|
else:
|
|
|
# 如果没有任何一个查询条件，则提示用户输入查询关键字
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
# 关闭数据库连接
|
|
|
session.close()
|
|
|
#添加出版社
|
|
|
def add_pushlisher(self):
|
|
|
session = Session()
|
|
|
email = self.publish_name_line.text()
|
|
|
address = self.publish_eamil_line.text()
|
|
|
name = self.publish_address_line.text()
|
|
|
if name and address and email :
|
|
|
new_publish = Publisher(name = name ,address = address,email= email)
|
|
|
session.add(new_publish)
|
|
|
session.commit()
|
|
|
else:
|
|
|
QMessageBox.information(self, '提示', '请填写完整的出版社信息。')
|
|
|
session.close()
|
|
|
#搜索图书
|
|
|
def search_borrow(self):
|
|
|
session = Session()
|
|
|
#获取用户输入的关键字
|
|
|
borrower = self.lineEdit_5.text()
|
|
|
book = self.lineEdit_6.text()
|
|
|
# 初始化查询条件列表
|
|
|
conditions = []
|
|
|
# 如果 name 不为 None，则添加书名筛选条件
|
|
|
if borrower:
|
|
|
conditions.append(Borrow.reader_id == borrower)
|
|
|
# 如果 id 不为 None，则添加书籍 ID 筛选条件
|
|
|
if book:
|
|
|
conditions.append(Borrow.book_id == book)
|
|
|
# 如果有任何一个查询条件，则执行查询
|
|
|
if conditions:
|
|
|
borrows = session.query(Borrow).filter(or_(*conditions))
|
|
|
# 如果找到了匹配的书籍
|
|
|
row = 0
|
|
|
for borrow in borrows:
|
|
|
readers = session.query(Reader).filter(Reader.id==borrow.reader_id)
|
|
|
for reader in readers:
|
|
|
reader_name = reader.name
|
|
|
books = session.query(Book).filter(Book.id==borrow.book_id)
|
|
|
for book in books:
|
|
|
book_name = book.title
|
|
|
|
|
|
self.tableWidget_5.setItem(row, 0, QTableWidgetItem(reader_name))
|
|
|
self.tableWidget_5.setItem(row, 1, QTableWidgetItem(book_name))
|
|
|
self.tableWidget_5.setItem(row, 2, QTableWidgetItem(str(borrow.borrow_date)))
|
|
|
|
|
|
row += 1
|
|
|
self.tableWidget.show()
|
|
|
if not row:
|
|
|
#如果没有找到匹配的书籍，弹出提示框
|
|
|
QMessageBox.information(self, '提示', '没有找到匹配的借阅记录。')
|
|
|
else:
|
|
|
# 如果没有任何一个查询条件，则提示用户输入查询关键字
|
|
|
QMessageBox.information(self, '提示', '请至少输入一个查询关键字。')
|
|
|
# 关闭数据库连接
|
|
|
session.close()
|
|
|
def buttonForRow(self, data_type):
|
|
|
widget = QWidget()
|
|
|
# 删除按钮
|
|
|
self.deleteBtn = QPushButton('删除')
|
|
|
self.deleteBtn.setStyleSheet(''' text-align : center;
|
|
|
background-color : LightCoral;
|
|
|
height : 30px;
|
|
|
border-style: outset;
|
|
|
font : 11px; ''')
|
|
|
self.deleteBtn.clicked.connect(lambda: self.handleDelete(data_type))
|
|
|
|
|
|
# 修改按钮
|
|
|
self.modifyBtn = QPushButton('修改')
|
|
|
self.modifyBtn.setStyleSheet(''' text-align : center;
|
|
|
background-color : LightSkyBlue;
|
|
|
height : 30px;
|
|
|
border-style: outset;
|
|
|
font : 11px; ''')
|
|
|
self.modifyBtn.clicked.connect(lambda: self.handleModify(data_type))
|
|
|
|
|
|
hLayout = QHBoxLayout()
|
|
|
hLayout.addWidget(self.modifyBtn)
|
|
|
hLayout.addWidget(self.deleteBtn)
|
|
|
hLayout.setContentsMargins(5, 2, 5, 2)
|
|
|
|
|
|
widget.setLayout(hLayout)
|
|
|
return widget
|
|
|
|
|
|
def borrowForRow(self):
|
|
|
widget = QWidget()
|
|
|
# 借阅
|
|
|
self.borrowBtn = QPushButton('借阅')
|
|
|
self.borrowBtn.setStyleSheet(''' text-align : center;
|
|
|
background-color : LightCoral;
|
|
|
height : 30px;
|
|
|
border-style: outset;
|
|
|
font : 13px; ''')
|
|
|
self.borrowBtn.clicked.connect(self.user_borrow)
|
|
|
hLayout =QHBoxLayout()
|
|
|
hLayout.addWidget(self.borrowBtn)
|
|
|
hLayout.setContentsMargins(5, 2, 5, 2)
|
|
|
widget.setLayout(hLayout)
|
|
|
return widget
|
|
|
|
|
|
def returnForRow(self):
|
|
|
widget = QWidget()
|
|
|
# 归还
|
|
|
self.returnBtn = QPushButton('归还')
|
|
|
self.returnBtn.setStyleSheet(''' text-align : center;
|
|
|
background-color : LightCoral;
|
|
|
height : 30px;
|
|
|
border-style: outset;
|
|
|
font : 13px; ''')
|
|
|
self.returnBtn.clicked.connect(self.user_return)
|
|
|
hLayout =QHBoxLayout()
|
|
|
hLayout.addWidget(self.returnBtn)
|
|
|
hLayout.setContentsMargins(5, 2, 5, 2)
|
|
|
widget.setLayout(hLayout)
|
|
|
return widget
|
|
|
|
|
|
def user_return(self):
|
|
|
row = self.user_window.tableWidget_2.indexAt(self.returnBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
name = self.user_window.tableWidget_2.item(row, 0).text()
|
|
|
session = Session()
|
|
|
try:
|
|
|
book = session.query(Book).filter_by(title = name).first()
|
|
|
borrow = session.query(Borrow).filter_by(reader_id=self.user_account, book_id=book.id).first()
|
|
|
if borrow:
|
|
|
# 更新借阅记录的归还时间
|
|
|
borrow.return_date = datetime.now()
|
|
|
# 更新书籍信息表中该书籍的可借数量
|
|
|
book = borrow.book
|
|
|
book.number += 1
|
|
|
session.delete(borrow)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', '归还成功')
|
|
|
self.user_window.tableWidget_2.removeRow(row) # 从表格中移除该行数据
|
|
|
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'归还失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
def user_borrow(self):
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.user_window.tableWidget.indexAt(self.borrowBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
id = self.user_window.tableWidget.item(row, 0).text()
|
|
|
session = Session()
|
|
|
try:
|
|
|
book = session.query(Book).filter_by(id=id).first()
|
|
|
if book.number >0:
|
|
|
book.number -= 1
|
|
|
session.commit() # 提交修改
|
|
|
self.user_window.tableWidget.setItem(row, 7, QTableWidgetItem(str(book.number)))
|
|
|
QMessageBox.information(self, '提示', '借阅成功')
|
|
|
borrow_date = datetime.now()
|
|
|
return_date = borrow_date + timedelta(days=30)
|
|
|
borrow = Borrow(reader_id=self.user_account, book_id=id, borrow_date=borrow_date, return_date=return_date)
|
|
|
session.add(borrow) # 添加借阅信息记录
|
|
|
session.commit() # 提交修改
|
|
|
else :
|
|
|
QMessageBox.information(self, '提示', '该书已无剩余')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'删除失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
def handleDelete(self, data_type):
|
|
|
if data_type == 'book':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget.indexAt(self.deleteBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
id = self.tableWidget.item(row, 0).text()
|
|
|
# 删除数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
book = session.query(Book).filter_by(id=id).first()
|
|
|
relation = session.query(Book_tag).filter_by(book_id=id).first()
|
|
|
|
|
|
# 从数据库中删除这些书籍session.delete(book)
|
|
|
session.delete(book)
|
|
|
session.delete(relation)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{book.title} 删除成功')
|
|
|
self.tableWidget.removeRow(row) # 从表格中移除该行数据
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'删除失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'publisher':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_2.indexAt(self.deleteBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
name = self.tableWidget_2.item(row, 0).text()
|
|
|
# 删除数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
# 查询与该出版社关联的书籍
|
|
|
publish = session.query(Publisher).filter_by(name=name).first()
|
|
|
books = session.query(Book).filter_by(publisher=publish)
|
|
|
# 从数据库中删除这些书籍
|
|
|
for book in books:
|
|
|
session.delete(book)
|
|
|
session.delete(publish)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{name} 删除成功')
|
|
|
self.tableWidget_2.removeRow(row) # 从表格中移除该行数据
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'删除失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'reader':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_4.indexAt(self.deleteBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
id = self.tableWidget_4.item(row, 1).text()
|
|
|
# 删除数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
readers = session.query(Reader).filter_by(id=id).first()
|
|
|
# 从数据库中删除这些书籍
|
|
|
session.delete(readers)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{readers.name} 删除成功')
|
|
|
self.tableWidget_4.removeRow(row) # 从表格中移除该行数据
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'删除失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
elif data_type == 'tag':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_3.indexAt(self.deleteBtn.pos()).row()
|
|
|
# 获取该行对应的记录的标签名
|
|
|
name = self.tableWidget_3.item(row, 2).text()
|
|
|
# 删除数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
tags = session.query(Tag).filter_by(name=name).first()
|
|
|
# 从数据库中删除这些书籍
|
|
|
session.delete(tags)
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{tags.name} 删除成功')
|
|
|
self.tableWidget_3.removeRow(row) # 从表格中移除该行数据
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'删除失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'tag_type':
|
|
|
QMessageBox.information(self, '提示', "该分类不能删除！")
|
|
|
|
|
|
def handleModify(self, data_type):
|
|
|
if data_type == 'book':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget.indexAt(self.modifyBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
id = self.tableWidget.item(row, 0).text()
|
|
|
# 查询数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
book = session.query(Book).filter_by(id=id).first()
|
|
|
# 弹出对话框，让用户输入修改后的属性值
|
|
|
title, ok1 = QInputDialog.getText(self, "修改", f"书名({book.title})")
|
|
|
author, ok2 = QInputDialog.getText(self, "修改", f"作者({book.author})")
|
|
|
publisher, ok3 = QInputDialog.getText(self, "修改", f"出版社({book.publisher.name})")
|
|
|
description, ok4 = QInputDialog.getText(self, "修改", f"简介({book.description})")
|
|
|
price, ok5 = QInputDialog.getText(self, "修改", f"价格({book.price})")
|
|
|
number,ok6 = QInputDialog.getText(self, "修改", f"剩余数量({book.number})")
|
|
|
tag,ok7 = QInputDialog.getText(self, "修改", f"标签({book.tag})")
|
|
|
# 如果用户按下了取消，则不做修改
|
|
|
if not (ok1 and ok2 and ok3 and ok4 and ok5 and ok6 and ok7):
|
|
|
return
|
|
|
# 更新数据
|
|
|
book.title = title.strip() if title.strip() else book.title
|
|
|
book.author = author.strip() if author.strip() else book.author
|
|
|
book.publish_name = publisher.strip() if publisher.strip() else book.publish_name
|
|
|
book.price = float(price.strip()) if price.strip() else book.price
|
|
|
book.description = description.strip() if description.strip() else book.description
|
|
|
book.number = number.strip() if number.strip() else book.number
|
|
|
book.tag = tag.strip() if tag.strip() else book.tag
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{book.title} 修改成功')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'修改失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'publisher':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_2.indexAt(self.modifyBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
name = self.tableWidget_2.item(row, 0).text()
|
|
|
print(name)
|
|
|
# 查询数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
publisher = session.query(Publisher).filter_by(name=name).first()
|
|
|
# 弹出对话框，让用户输入修改后的属性值
|
|
|
new_name, ok = QInputDialog.getText(self, "修改", f"出版社名称({publisher.name})")
|
|
|
new_address ,ok1 = QInputDialog.getText(self, "修改", f"出版社地址({publisher.address})")
|
|
|
new_email ,ok2 = QInputDialog.getText(self, "修改", f"出版社邮箱({publisher.email})")
|
|
|
# 如果用户按下了取消，则不做修改
|
|
|
if not ok and ok1 and ok2:
|
|
|
return
|
|
|
|
|
|
# 更新数据
|
|
|
publisher.name = new_name.strip() if new_name.strip() else publisher.name
|
|
|
publisher.address = new_address.strip() if new_address.strip() else publisher.address
|
|
|
publisher.email = new_email.strip() if new_email.strip() else publisher.email
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{name} 修改成功')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'修改失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'reader':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_4.indexAt(self.modifyBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
id = self.tableWidget_4.item(row, 1).text()
|
|
|
# 查询数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
reader = session.query(Reader).filter_by(id=id).first()
|
|
|
# 弹出对话框，让用户输入修改后的属性值
|
|
|
name, ok1 = QInputDialog.getText(self, "修改", f"读者名字({reader.name})")
|
|
|
sex ,ok2 = QInputDialog.getText(self, "修改", f"读者性别({reader.gender})")
|
|
|
add , ok3 = QInputDialog.getText(self, "修改", f"读者住址({reader.address})")
|
|
|
if not ok3 and ok1 and ok2:
|
|
|
return
|
|
|
reader.name = name.strip() if name.strip() else reader.name
|
|
|
reader.gender = sex.strip() if sex.strip() else reader.gender
|
|
|
reader.address = add.strip() if add.strip() else reader.address
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{name} 修改成功')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'修改失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'tag':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_3.indexAt(self.modifyBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
name = self.tableWidget_3.item(row, 2).text()
|
|
|
# 查询数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
tags = session.query(Tag).filter_by(name = name).first()
|
|
|
# 弹出对话框，让用户输入修改后的属性值
|
|
|
name, ok1 = QInputDialog.getText(self, "修改", f"标签名字({tags.name})")
|
|
|
if not ok1:
|
|
|
return
|
|
|
tags.name = name.strip() if name.strip() else tags.name
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{name} 修改成功')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'修改失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
|
|
|
elif data_type == 'tag_type':
|
|
|
# 获取当前点击的按钮所在的行数
|
|
|
row = self.tableWidget_3.indexAt(self.modifyBtn.pos()).row()
|
|
|
# 获取该行对应的记录的ID
|
|
|
name = self.tableWidget_3.item(row, 0).text()
|
|
|
# 查询数据
|
|
|
session = Session()
|
|
|
try:
|
|
|
tag_types = session.query(Tag_category).filter_by(name = name).first()
|
|
|
# 弹出对话框，让用户输入修改后的属性值
|
|
|
name, ok1 = QInputDialog.getText(self, "修改", f"标签名字({tag_types.name})")
|
|
|
if not ok1:
|
|
|
return
|
|
|
tag_types.name = name.strip() if name.strip() else tag_types.name
|
|
|
session.commit()
|
|
|
QMessageBox.information(self, '提示', f'{name} 修改成功')
|
|
|
except Exception as e:
|
|
|
session.rollback()
|
|
|
print(f"Error: {e}")
|
|
|
QMessageBox.information(self, '提示', f'修改失败: {e}')
|
|
|
finally:
|
|
|
session.close()
|
|
|
#数据库
|
|
|
engine = create_engine('sqlite:///library.db')
|
|
|
Base = declarative_base()
|
|
|
|
|
|
# 书籍信息表模型
|
|
|
class Book(Base):
|
|
|
__tablename__ = 'book'
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
title = Column(Text, nullable=False)
|
|
|
author = Column(Text, nullable=False)
|
|
|
publish_name = Column(Text, ForeignKey('publisher.name'))
|
|
|
description = Column(Integer)
|
|
|
price = Column(Float(8, 2))
|
|
|
number = Column(Integer,default=1)
|
|
|
tag = Column(Integer,nullable=False)
|
|
|
|
|
|
publisher = relationship('Publisher', back_populates='books')
|
|
|
tags = relationship("Book_tag", back_populates="book")
|
|
|
|
|
|
# 出版社表模型
|
|
|
class Publisher(Base):
|
|
|
__tablename__ = 'publisher'
|
|
|
name = Column(Text, nullable=False, primary_key=True)
|
|
|
address = Column(Text)
|
|
|
email = Column(Text)
|
|
|
|
|
|
|
|
|
books = relationship('Book', back_populates='publisher')
|
|
|
|
|
|
#标签分类
|
|
|
class Tag_category(Base):
|
|
|
__tablename__ = 'tag_category'
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
name = Column(Text, nullable=False)
|
|
|
|
|
|
tags = relationship('Tag', back_populates='category')
|
|
|
|
|
|
|
|
|
#标签
|
|
|
class Tag(Base):
|
|
|
__tablename__ = 'tag'
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
name = Column(Text, nullable=False)
|
|
|
category_id = Column(Integer, ForeignKey('tag_category.id'))
|
|
|
|
|
|
category = relationship('Tag_category', back_populates='tags')
|
|
|
books = relationship("Book_tag", back_populates="tag")
|
|
|
|
|
|
class Book_tag(Base):
|
|
|
__tablename__='book_tag'
|
|
|
book_id = Column(Integer,ForeignKey('book.id'), primary_key=True, nullable=False)
|
|
|
tag_id = Column(Integer,ForeignKey('tag.id'), primary_key=True, nullable=False)
|
|
|
|
|
|
book = relationship("Book", back_populates="tags")
|
|
|
tag = relationship("Tag", back_populates="books")
|
|
|
|
|
|
class ReaderType(Base):
|
|
|
__tablename__ = 'reader_type'
|
|
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
name = Column(String(50), nullable=False)
|
|
|
max_borrow = Column(Integer)
|
|
|
borrow_days = Column(Integer)
|
|
|
renew_times = Column(Integer)
|
|
|
# 定义与读者信息表的关联关系
|
|
|
readers = relationship('Reader', backref='reader_type')
|
|
|
|
|
|
# 定义读者信息模型类
|
|
|
class Reader(Base):
|
|
|
__tablename__ = 'reader'
|
|
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
name = Column(String(50), nullable=False)
|
|
|
reader_type_id = Column(Integer, ForeignKey('reader_type.id', ondelete='CASCADE'), nullable=False)
|
|
|
gender = Column(String(10))
|
|
|
phone = Column(String(20))
|
|
|
email = Column(String(100))
|
|
|
address = Column(String(100))
|
|
|
is_admin = Column(Integer, server_default='0')
|
|
|
|
|
|
# 定义与借阅信息表的关联关系
|
|
|
borrows = relationship('Borrow', backref='reader')
|
|
|
|
|
|
# 定义借阅信息模型类
|
|
|
class Borrow(Base):
|
|
|
__tablename__ = 'borrow'
|
|
|
|
|
|
id = Column(Integer, primary_key=True)
|
|
|
reader_id = Column(Integer, ForeignKey('reader.id', ondelete='CASCADE'), nullable=False)
|
|
|
book_id = Column(Integer, ForeignKey('book.id', ondelete='CASCADE'), nullable=False)
|
|
|
borrow_date = Column(DateTime, nullable=False)
|
|
|
return_date = Column(DateTime, nullable=False)
|
|
|
# 定义与书籍信息表的关联关系
|
|
|
book = relationship('Book', backref='borrows')
|
|
|
|
|
|
Base.metadata.create_all(engine)
|
|
|
Session = sessionmaker(bind=engine)
|
|
|
|
|
|
if __name__ =='__main__':
|
|
|
app = QApplication([])
|
|
|
apply_stylesheet(app,theme="light_blue.xml")
|
|
|
window = mananger_Wiondow()
|
|
|
window.hide()
|
|
|
app.exec() |
