def add_user(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "insert into users(user_name, user_pwd, user_email) values(%s, %s, %s)"
user_name = self.add_user_name.text()
user_email = self.add_user_email.text()
user_pwd = self.add_user_pwd.text()
user_confirm_pwd = self.add_user_confirm_pwd.text()
self.error_message.setText("")
if user_pwd == user_confirm_pwd:
# 加盐
hash = hashlib.md5(bytes("禾木AI", encoding="utf-8"))
hash.update(bytes(user_pwd, encoding="utf-8"))
user_pwd_hash = hash.hexdigest()
# 4、执行语句
cur.execute(sql, (user_name, user_pwd_hash, user_email))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
# 7、消息提示
self.statusBar().showMessage('用户添加成功！')
self.add_user_name.setText('')
self.add_user_email.setText('')
self.add_user_pwd.setText('')
self.add_user_confirm_pwd.setText('')
else:
self.error_message.setText("两次密码不一致")
self.add_user_pwd.setText('')
self.add_user_confirm_pwd.setText('')
def export_day_operations(self):
conn = get_conn()
cur = conn.cursor()
sql = "select book_name, client_name, type, day_from, day_to from dayoperations"
cur.execute(sql)
data = cur.fetchall()
wb = Workbook("day_operations.xlsx")
sheet1 = wb.add_worksheet()
sheet1.write(0, 0, "书名")
sheet1.write(0, 1, "客户")
sheet1.write(0, 2, "类型")
sheet1.write(0, 3, "开始时间")
sheet1.write(0, 4, "结束时间")
row_number = 1
for row in data:
column_number = 0
for item in row:
sheet1.write(row_number, column_number, str(item))
column_number += 1
row_number += 1
wb.close()
self.statusBar().showMessage("数据导出成功！")
def editor_user(self):
old_name = self.old_user_name.text()
new_name = self.editor_user_name.text()
new_email = self.editor_user_email.text()
user_pwd = self.eidtor_user_pwd.text()
user_confirm_pwd = self.editor_user_confirm_pwd.text()
if user_pwd == user_confirm_pwd:
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
if user_pwd == user_confirm_pwd:
# 加盐
hash = hashlib.md5(bytes("禾木AI", encoding="utf-8"))
hash.update(bytes(user_pwd, encoding="utf-8"))
user_pwd_hash = hash.hexdigest()
sql = "update users set user_name=%s, user_email=%s, user_pwd=%s where user_name=%s"
# 4、执行语句
cur.execute(sql,
(new_name, new_email, user_pwd_hash, old_name))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
self.statusBar().showMessage('用户修改成功！')
self.user_groupBox.setEnabled(False)
else:
self.statusBar().showMessage('两次密码不一致！')
def editor_book(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "update book set book_name=%s, book_description=%s, book_code=%s,book_category=%s, " \
"book_author=%s, book_publisher=%s, book_price=%s where book_name=%s"
book_name = self.editor_book_name.text()
book_description = self.editor_book_desc.toPlainText()
book_code = self.editor_book_code.text()
book_category = self.editor_book_category.currentText()
book_author = self.editor_book_author.currentText()
book_publisher = self.editor_book_publisher.currentText()
book_price = self.editor_book_price.text()
book_old_name = self.editor_search_name.text()
# 4、执行语句
cur.execute(sql,
(book_name, book_description, book_code, book_category,
book_author, book_publisher, book_price, book_old_name))
# 5、insert、update、delete必须显示提交
conn.commit()
self.show_books()
self.statusBar().showMessage('图书修改成功！')
def editor_client(self):
old_client_id = self.old_client_id.text()
client_name = self.editor_client_name.text()
client_phone = self.editor_client_phone.text()
client_id = self.editor_client_id.text()
regex = re.compile(r"(^0\d{2,3}-\d{7,8}$)")
if re.match(regex, client_phone):
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "update client set client_name=%s, client_phone=%s, client_id=%s " \
"where client_id=%s"
# 4、执行语句
cur.execute(sql,
(client_name, client_phone, client_id, old_client_id))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
self.statusBar().showMessage("修改成功！")
self.show_client()
else:
self.statusBar().showMessage("电话格式位区号-电话")
def add_book(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "insert into book(book_name, book_description, book_code,book_category, " \
"book_author, book_publisher, book_price) values(%s, %s, %s, %s, %s, %s, %s)"
book_name = self.add_book_name.text()
book_description = self.add_book_desc.toPlainText()
book_code = self.add_book_code.text()
book_category = self.add_book_category.currentText()
book_author = self.add_book_author.currentText()
book_publisher = self.add_book_publisher.currentText()
book_price = self.add_book_price.text()
# 4、执行语句
cur.execute(sql,
(book_name, book_description, book_code, book_category,
book_author, book_publisher, book_price))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
self.add_book_name.setText('')
self.add_book_desc.setPlainText('')
self.add_book_code.setText('')
self.add_book_category.setCurrentIndex(0)
self.add_book_author.setCurrentIndex(0)
self.add_book_publisher.setCurrentIndex(0)
self.add_book_price.setText('')
self.statusBar().showMessage('图书添加成功！')
self.add_publisher_name.setText('')
self.show_books()
def select_client(self):
conn = get_conn()
cur = conn.cursor()
sql = "select client_name, client_phone, client_id from client where client_id = %s"
old_client_id = self.old_client_id.text()
cur.execute(sql, (old_client_id, ))
data = cur.fetchone()
if data:
self.editor_client_name.setText(data[0])
self.editor_client_phone.setText(data[1])
self.editor_client_id.setText(data[2])
def show_publisher_combobox(self):
conn = get_conn()
cur = conn.cursor()
sql = "select publisher_name from publisher"
cur.execute(sql)
data = cur.fetchall()
if data:
self.add_book_publisher.clear()
self.editor_book_publisher.clear()
for publisher in data:
self.add_book_publisher.addItem(publisher[0])
self.editor_book_publisher.addItem(publisher[0])
def show_author_combobox(self):
conn = get_conn()
cur = conn.cursor()
sql = "select author_name from author"
cur.execute(sql)
data = cur.fetchall()
if data:
self.add_book_author.clear()
self.editor_book_author.clear()
for author in data:
self.add_book_author.addItem(author[0])
self.editor_book_author.addItem(author[0])
def show_category_combobox(self):
conn = get_conn()
cur = conn.cursor()
sql = "select category_name from category"
cur.execute(sql)
data = cur.fetchall()
if data:
self.add_book_category.clear()
self.editor_book_category.clear()
for category in data:
self.add_book_category.addItem(category[0])
self.editor_book_category.addItem(category[0])
def user_login(self):
conn = get_conn()
cur = conn.cursor()
sql = "select * from users where user_name=%s and user_pwd=%s"
old_user_name = self.old_user_name.text()
editor_pwd = self.editor_pwd.text()
pwd = self.md5(editor_pwd)
cur.execute(sql, (old_user_name, pwd))
data = cur.fetchone()
if data:
self.user_groupBox.setEnabled(True)
self.editor_user_name.setText(data[1])
self.editor_user_email.setText(data[3])
def handel_login(self):
conn = get_conn()
cur = conn.cursor()
sql = "select * from users where user_name=%s and user_pwd=%s"
user_name = self.username.text()
pwd = self.pwd.text()
pwd = self.md5(pwd)
cur.execute(sql, (user_name, pwd))
data = cur.fetchone()
if data:
self.main_app = MainApp()
self.close()
self.main_app.show()
else:
self.error_message.setText("用户名或密码错误，重新输入")
def delete_book(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
sql = "delete from book where book_name = %s"
book_name = self.editor_book_name.text()
warning = QMessageBox.warning(self, '删除图书', '你确定要删除',
QMessageBox.Yes | QMessageBox.No)
if warning == QMessageBox.Yes:
cur.execute(sql, (book_name, ))
conn.commit()
close_conn(conn, cur)
self.show_books()
self.statusBar().showMessage('图书成功删除')
def show_all_operations(self):
conn = get_conn()
cur = conn.cursor()
sql = "select book_name, client_name, type, day_from, day_to from dayoperations"
cur.execute(sql)
data = cur.fetchall()
if data:
self.operation_table.setRowCount(0)
self.operation_table.insertRow(0)
for row, form in enumerate(data):
for column, item in enumerate(form):
self.operation_table.setItem(row, column,
QTableWidgetItem(str(item)))
column += 1
row_position = self.operation_table.rowCount()
self.operation_table.insertRow(row_position)
def delete_client(self):
old_client_id = self.old_client_id.text()
warning = QMessageBox.warning(self, "删除客户", "你确定删除吗?",
QMessageBox.Yes | QMessageBox.No)
if warning == QMessageBox.Yes:
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "delete from client where client_id = %s"
# 4、执行语句
cur.execute(sql, (old_client_id, ))
# 5、insert、update、delete必须显示提交
conn.commit()
close_conn(cur, conn)
self.statusBar().showMessage("删除客户成功！")
self.show_client()
def search_book(self):
conn = get_conn()
cur = conn.cursor()
sql = "select * from book where book_name = %s"
book_name = self.editor_search_name.text()
cur.execute(sql, (book_name, ))
data = cur.fetchone()
if data:
self.editor_book_name.setText(data[1])
self.editor_book_desc.setPlainText(data[2])
self.editor_book_category.setCurrentText(data[3])
self.editor_book_author.setCurrentText(data[4])
self.editor_book_publisher.setCurrentText(data[5])
self.editor_book_price.setText(str(data[6]))
self.editor_book_code.setText(data[7])
else:
self.statusBar().showMessage('没有这个书籍')
def show_client(self):
conn = get_conn()
cur = conn.cursor()
sql = "select client_name, client_phone, client_id from client"
cur.execute(sql)
data = cur.fetchall()
if data:
self.client_table.setRowCount(0)
self.client_table.insertRow(0)
for row, form in enumerate(data):
for column, item in enumerate(form):
self.client_table.setItem(row, column,
QTableWidgetItem(str(item)))
column += 1
row_position = self.client_table.rowCount()
self.client_table.insertRow(row_position)
def show_publisher(self):
conn = get_conn()
cur = conn.cursor()
sql = "select publisher_name from publisher"
cur.execute(sql)
data = cur.fetchall()
if data:
self.publisher_table.setRowCount(0)
self.publisher_table.insertRow(0)
for row, form in enumerate(data):
for column, item in enumerate(form):
self.publisher_table.setItem(row, column,
QTableWidgetItem(str(item)))
column += 1
row_position = self.publisher_table.rowCount()
self.publisher_table.insertRow(row_position)
def add_publisher(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "insert into publisher(publisher_name) values(%s)"
publisher_name = self.add_publisher_name.text()
# 4、执行语句
cur.execute(sql, (publisher_name, ))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
# 7、消息提示
self.statusBar().showMessage('作者添加成功！')
self.add_publisher_name.setText('')
self.show_publisher()
def handel_day_operation(self):
book_name = self.book_name.text()
client_name = self.client_name.text()
operate_type = self.tpye_combox.currentText()
day_number = self.day_combox.currentIndex() + 1
today_date = datetime.date.today()
to_day = today_date + datetime.timedelta(days=day_number)
# 数据库操作
conn = get_conn()
cur = conn.cursor()
sql = "insert into dayoperations(book_name, client_name, type, days, day_from, day_to)" \
"values(%s, %s, %s, %s, %s, %s)"
cur.execute(sql, (book_name, client_name, operate_type, day_number,
today_date, to_day))
conn.commit()
close_conn(conn, cur)
self.statusBar().showMessage("操作添加成功！")
self.show_all_operations()
def show_books(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
sql = "select book_code, book_name, book_description, book_category," \
" book_author, book_publisher, book_price from book"
cur.execute(sql)
data = cur.fetchall()
self.book_table.setRowCount(0)
self.book_table.insertRow(0)
for row, form in enumerate(data):
for column, item in enumerate(form):
self.book_table.setItem(row, column,
QTableWidgetItem(str(item)))
column += 1
row_postion = self.book_table.rowCount()
self.book_table.insertRow(row_postion)
def add_client(self):
# 数据库操作流程
# 1、获取连接
conn = get_conn()
# 2、获取cursor
cur = conn.cursor()
# 3、SQl语句
sql = "insert into client(client_name, client_phone, client_id) values(%s, %s, %s)"
client_name = self.add_client_name.text()
client_phone = self.add_client_phone.text()
client_id = self.add_client_id.text()
# 4、执行语句
cur.execute(sql, (client_name, client_phone, client_id))
# 5、insert、update、delete必须显示提交
conn.commit()
# 6、关闭资源
close_conn(conn, cur)
# 7、消息提示
self.statusBar().showMessage('客户添加成功！')
self.add_client_name.setText('')
self.add_client_phone.setText('')
self.add_client_id.setText('')
self.show_client()
